Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA Commands For ActiveX Controls


I have made an Excel sheet that uses ActiveX controls (Option Box and Check Box). I have included a couple of screen caps at the bottom of this post to better explain the layout. Here are the specifics:

There is one Option Box Group labeled "InputOptionGroup" with choices labeled: "FirstOption", "SecondOption", "RefundOption" and "NoneOption".

Adjacent to the Option Box is a series of Check Boxes that correspond to the choice made in the Option Box with the exception of "NoneOption". The Check Boxes are labeled "FirstCheck", "SecondCheck" and "RefundCheck". Each Check Box object belongs to the group labeled "OutputCheckGroup".

I do know that the value of the Option Box choices and Check Boxes can be either True or False depending on if it has been chosen or marked, but I do not know how to incorporate these values into the action script. With that being said, here's what I'm trying to accomplish:

...when "FirstOption" is chosen, clear values of all Check Boxes and place a check mark in "FirstCheck".
...when "SecondOption" is chosen, clear values of all Check Boxes and place a check mark in "SecondCheck".
...when "RefundOption" is chosen, clear values of all Check Boxes and place a check mark in "RefundCheck".
...when "NoneOption" is chosen, clear values of all Check Boxes (no furthur action).

Also, there are a couple more requests:Is there a way to lock the Check Boxes from user input? I want the Check Box values to be altered only by the choice in the Option Box.
Take the "GPIN" field, the one on the right is locked from user input. This particular cell references user input from the "GPIN" field on the left. Is there a way to return a blank value when the input field is blank insted of returning a "0"? If you notice, all fields on the right reference blank values as "0".
In the left "Amount" field, I need to display a "$" followed by the value in the input "Amount" field


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Conditional Disabling Of ActiveX Controls
I want to do disable certain ActiveX toggles depending on the value of a specific cell; this cell can contain more than one value that should disable that control. the logic works as follows:

IF Cell="A" THEN Toggle 1=Disabled AND Toggle 2=Disabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="B" THEN Toggle 1=Ensabled AND Toggle 2=Enabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="C" THEN Toggle 1=Enabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="D" OR Cell="E" THEN Toggle 1=Disabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Enabled AND Toggle 5-Enabled


View Replies!   View Related
Running Macros And Activex Controls
Here is a sample of code I am having problems with:

Sub CheckBox1_Click()
Range("Make") = Range("Make1").Value
Range("Model") = Range("Model1").Value
Application.Run ("EnterPrice")
Aplication.Run ("Unit Selection")
CheckBox1.Value = True
End Sub

I am having problems with the named ranges "Make" and "Model" which are on another sheet and with runninh the "EnterPrice" and "Unit Selection" Macros which are in Module1, I get the error message "Method 'Range' of object '_Worksheet' class failed"

View Replies!   View Related
Deleting VBA Modules And ActiveX Controls
When I try to open a worksheet that has any sort of macros I get the "error opening file" popup with the request to send the error to Microsoft.

When I check the box to "recover and save file" and proceed I am told that repairs were necessary to save the file

The repairs stated are " LOST VBA MODULES" and "LOST ACTIVEX CONTROLS"

I have not changed anything to my knowledge.

Changine the macro security settings to low has no effect either

View Replies!   View Related
ActiveX Controls On Worksheet - Fmbackstyle
I have problems wit setting some ActiveX controls properties. If I place ActiveX on worksheet manually, it is diferrent object, then If I place it by VBA code. I, am not able to open properities window for controls created by VBA. When I open properties window for ActiveX created manually, there is property fmbackstyle. If I am trying to set this property by VBA for controls created by VBA, result is error.

View Replies!   View Related
ActiveX Sheet Controls Basics
i have been working on editting a game that i got off the internet and i was having trouble editing the buttons on the worksheet. they dont seem to be linked to a macro and i cant edit them or even delete them
it seems like they are forms but attached to the worksheet.

View Replies!   View Related
Loop Through ActiveX Controls In Worksheet
I'm looking for a way to loop through the ActiveX controls (option buttons) in one sheet so that I can have these option buttons reflect what I select on the front sheet.

View Replies!   View Related
Programmatically Create ActiveX Worksheet Controls
I created a macro to create command buttons on sheet 1 of my worksheet. The created buttons using oleobject and class type: forms.commandbutton1, creates an object with no properties on the sheet (i.e. if you right click on it excel thinks it's an object and not a control.) Is there a way to create it as a control?

View Replies!   View Related
MIN & MAX VBA Commands Not Seeming To Work
I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.

In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.

View Replies!   View Related
Commands And Toolbars Gone
I lost my command bar and tool bars (excel 97) all I have is the blue bar on top then the column letters. How do I restore them?

View Replies!   View Related
Removing File Commands
I am trying to remove File Commands from this specific workbook and force save and send functions through command buttons.

This is the code I tried in the workbook module to remove file commands(Excel 2003):
__________
Sub Workbook_Open()
Dim a As CommandBar
For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = False
Next
Application.CommandBars("File").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False........



View Replies!   View Related
Open And Enter Commands
Is there any way that i could open and enter commands in ms dos
controlling from excel vba code?, like, say i wanted to change the current
directory in msdos from d: to c:, is there anyway that i could do it from
excel vba?

View Replies!   View Related
Missing Menu Commands
I have run into a problem with the Insert menu.

In the submenu
Cells
Rows
Columns
Worksheet

are "Greyed out" and therefore not available.

This happens when the application is first opened - no code is running it seems to be associated with the Personal workbook?

I have not encountered this previously.

IT help desk was its usual high standard - ZIP

I have searched this board without success.

The Microsoft Help functions were also silent.

View Replies!   View Related
Executing Two Different Commands With Same Button
I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".

View Replies!   View Related
VBA Macro- Apply Set Of Commands
Sub elaseval()
For each cell in Range(“B7:B50”)
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C” & cell.Row : ”T” & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub

I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.

View Replies!   View Related
Disable Menu Commands
I need to disable the "save as" file menu command when a user opens a shared document. Upon exit I want to enable the save as so that other opened worksheets are not affected. This code works: CommandBars("File").Controls("Save As...").Enabled = False

My problem is this. I can activate this command by selecting run in the VB editor.
But I need for this action to be automatic when a user opens the worksheet. I did this in the editor:

Under general

Sub dISABLESAVEAS()
CommandBars("File").Controls("Save As...").Enabled = False
End

Sub WORKSHEET_BEFORECLOSE()
CommandBars("File").Controls("Save As...").Enabled = True
End

View Replies!   View Related
To Access Non-native Menu Commands In VBA
I have not been able to find any references to anywhere, so here I am....

I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.

View Replies!   View Related
Multiple Vba Commands On A Single Line
Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?

I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:

Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"


View Replies!   View Related
IF-formula- IF Function Only Manage 7 Commands
I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true?
And how do I write the formula,

View Replies!   View Related
Adding Commands To Right Mouse Clicks
How do you add commands to a right mouse click?

I click on a cell, do a right mouse click and would like to be able to select a command that puts the current date and/or time in the selected cell.

View Replies!   View Related
Running Userforms , But Don't Know The Commands To Make Them Run
I've created userforms, but don't know the commands to make them run.

View Replies!   View Related
Issuing Word Commands From Excel Macro
I am trying to build a word document from an excel macro (with some success) and I am running into some issues with excel not understanding some of the word macro commands. An excellent example of this is when I am trying to insert a page break as seen in the below code snippet:

'open word and create a new document
Dim wdApp As Object
Set wdApp = CreateObject("Word.application")
wdApp.documents.Add

'put some text into the document
wdApp.Selection.typetext Text:="Document title"
wdApp.Selection.TypeParagraph
'insert a page break
wdApp.Selection.InsertBreak Type:=wdPageBreak

I know the insert break command works fine because I can get it to work within a word macro. When trying to get excel to run it in word is another story. I get the following error message:

run-time error '9118':
Parameter value was out of acctable range

Debug
wdPageBreak = empty

View Replies!   View Related
Cell Colour Pallete Codes For Commands
where I can find the colour pallette codes for commands like, as my suck it see method of guessing the right colour is pretty time cosuming.
selection.Interior.ColorIndex = 34

and secondly, can rgb be used to fill cells colour i.e. something like
range.colour or background whatever the command is. rgb(255,255,255)


View Replies!   View Related
Recorded Macro Missing Paste Commands
I have been using the "record macro feature". What I want the macro to do is as follows.
1. cut all data from column C and paste it into G.
2. Use the textTocolumns feature to split a comma delimited string into 2 seperate columns.
3. user the textToColumns feature to remove any leading spaces from column G.
4. cut columns d-H and shift them left startign in column C.

I can do all of this find bu using the GUI while recording the macro but when I check the vb script it's completely missing the paste commands.

When I try and run the macro it, quite rightly, returns "run-time error '1004':No data was selected to parse.

Below is the resulting recorded macro.

How do I amend this macro do include pasting the cut data?

View Replies!   View Related
Formatting Keyboard Commands (negatives In Red With Parenthesis)
I use keyboard commands whenever possible. They are much quicker than the mouse.

For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.

I like to see negatives in red with parenthesis.

View Replies!   View Related
Multiple Worksheet_Change Commands On The Same Work Sheet
I have 2 cells on the same page of the worksheet that I would like to have a popup box flag if the user has entered a number over 100%. In this example the value is hard coded in cell A41 as 100% and can not be changed by the user. The entry cells are A14 and A15. The code works fine if I comment out one of the Worksheet_Change statements.

Is there a way to have 2 worksheet_Change statements on the same sheet or do I have to combine the if statement. My attempts at this point to combine the if statements have not worked.

Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox for the % value range READS
Select Case Target.Address
Case "$H$14"
If Range("H14").Value > Range("A41").Value Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "This value cannot exceed 100%.", vbCritical, "ERROR"
End If
Exit Sub
Case Else
Exit Sub
End Select
End Sub

View Replies!   View Related
Modify Right-Click Menu Built-in Commands
I am developing a program for my electrical calculations (electrical panel). In the program, on my worksheet, I need all row inserts/deletes being watched and logged into another worksheet. I have become to a conclusion it would be best done by "taking over" default behaviour of built-in right-click menu commands like "Copy", "Paste" and "Delete".

In the ozgrid tips page I found some solutions how to deactivate those commands, but that is not what is best for me - when the menu item (or a corresponding key) is pressed, I would like to check if the ActiveCell (or ActiveRow) meats some criteria, and acording to that, make some changes in my "log" - and after - let further actions to default command behaviour. I think of it like "BeforeDelete", "BeforeInsert" or something. In fact the "log" is the worksheet with coordinates of my tables from the first worksheet, which should be changed according to new size of a table after the row insert or delete.

View Replies!   View Related
ActiveX Collections Via VBA
I have a workbook which is a reasonably standard financial model (PL, BS, CF, etc). I have a control worksheet with a number of AcitveX controls (Command Buttons, Toggle Buttons and Combo Boxes) that drive the rest of the model (e.g., depreciation methods, trade terms, etc).

When the workbook opens I want to fill the lists of the various ComboBoxes, but I can't work out how to access JUST THE COMBOBOXES as a defined collection (e.g., MyWkSht.ComboBoxes[Index] or some such), or as a sub-type of a meta-collection (e.g., MyWkSht.Controls with Type="ComboBox"), without referring to them directly by name (e.g., MyWkSht.MyComboBox).

The ComboBoxes are NOT on a UserForm, but are embedded on the sheet as OLE objects.

I really don't want to have to hard-code the combobox names in directly, because I may need to add/remove them as the model develops - and anyway it's just WAY inelegant.

View Replies!   View Related
Use Activex Combobox In The Worksheet
successfully use Activex combobox on the worksheet, (excel 2003)
without the code from other boxes being triggered at random.

Or should I give this up, and use a form to house these controls? ( I have no Knowledge on this)

I've tried certain IF then statements to protect the excecutable, but it seems, that, even boxes in other books are looked at, with a simple call statement (to a macro in module)

Having a real problem in Isolating these things.

Try to get the code to activate when you select the same Item twice, It seems none of the procedures do this. I made a workaround with using two procedures, but it's a bit clumsy.

View Replies!   View Related
ActiveX Combobox Code-run
I've created a workbook on whose main sheet there are three activex comboboxes. The first one contains a list of years, and the second one contains a list of months. The user selects the year, and it goes to its linked cell. The user then selects a month, and using the Combobox_Click event, should run a sequence of 7 macros (i.e. using the "call updateinformation1" macro, which is the first of the seven updateinformation macros).

The sequence of macros ran fine before I added the comboboxes. After adding the comboboxes, the final (7th) macro somehow goes back to the first macro and then causes a "ClearContents method of Range class failed" error on the "Selection.ClearContents" line. These are the first and seventh macros:

Sub updateinformation1()
'clears previous information to make way for new information
Application.ScreenUpdating = False
Application.EnableEvents = False

Worksheets("Criteria2").Activate
Cells.Select
Selection.ClearContents

I can't for the life of me figure out why it's looping back to the first macro instead of just stopping after the seventh. I have other workbooks that do something similar, and I don't have this problem. Am I missing something?

if I need to post all 7 macros?

View Replies!   View Related
Refreshing Activex Textbox
I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.

View Replies!   View Related
Unwanted ActiveX Warning
I have used the OWC.Spreadsheet 11 object in a form. But since then, I get a warning, usually when I open the spreadsheet, saying "This application is about to initialize an ActiveX Control that might be unsafe. If you trust the source of this file Select OK.etc,etc". I am using Excel 2003. Is there anyway of getting rid of this message? I am worried that users will see it, worry about using it and press cancel instead of OK at the prompt, thus rendering my form useless.

View Replies!   View Related
ActiveX Control Can't Create Object
I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my

View Replies!   View Related
Activex Combo Box Format Text
How do I format the text in an ActiveX combo box? I would like to increase the font.

View Replies!   View Related
ActiveX Command Button Text Looks Bad
I have created an ActiveX command button and the text on the button looks crude. I know that is not very descriptive but I am unsure how to describe it. The text looks jagged and some letters are missing chunks of their letters. The font I have chosen is Calibri.

If I create a Form Controls button I do not have this problem but I want the button to have a particular background color so I do not believe I can use a Form Controls button.

I do not know why the other Calibri text on the worksheet looks fine while the ActiveX Calibri text does not. The attached image shows the two buttons with the top one being the ActiveX button while the bottom is a Forms button.

View Replies!   View Related
Transparent Background Of ActiveX Control
I have an ActiveX Label control with a transparent background. When I click on the label the background turns white and stays that way as long as the mouse is over the label, obscuring whatever's underneath. (It also very slightly alters the character spacing.) What object/property controls this? How do I adjust it?

While I'm here, is there an easy programmatic way to assign arbitrary RGB colors to ActiveX controls? The palettes available from the Properties window are fairly limited. A google search for "ActiveX colors" yields a deluge of marketed products, so it's hard to find useful information.

View Replies!   View Related
Can't Change Combobox Properties (activex)
How do I set the font size property and special effect property in this code?

I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.

Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals    If rCell.Validation.Type = 3 Then        lTop = rCell.Top        lLeft = rCell.Left        lHeight = rCell.Rows.Height        lWidth = rCell.Columns.Width            With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight)                .Name = "NewCombo" & lCount                .ListFillRange = rCell.Validation.Formula1                .LinkedCell = rCell.Address(0, 0)                '.SpecialEffect = fmSpecialEffectFlat //doesn't work //                '.FontSize = 14 //doesn't work //                '.Font.Size = 14 // doesn't work //            End With                        lCount = lCount + 1    End IfNext rCellEnd Sub

View Replies!   View Related
Reference ActiveX ComboBox Value On Worksheet
How do I reference the value contained within a combobox using VBA?

I have tried the following:

ActiveSheet. OLEObjects("cmbSearchType").Object.Value
ActiveSheet.OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Object.Value

I'm wanting to use the drop-down combo box as a simple way for the user to choose what type of search they wish to perform on the spreadsheet. The search algorithm chosen depends upon the current value show in the combo box.

I am using Excel 2007.

View Replies!   View Related
ActiveX Comboboxes Change To Icon
I have one sheet with about 100 activex controls (and another few 100 on the other sheets in the file), most combobox, but some checkbox as well. There are 30 of them lined up, one in each row. One at a time, one control will basically change to an icon only. If you select it in design mode, the name shows in the range name area of the toolbar, but when you select properties you get the properties for the sheet. Effectively I have an icon with nothing linked to it anymore.

For a while, it seemed to be somewhat random. But for the last few days, it is now cascading down. First combobox #6, then #7, etc. I'm now on #11 or so. Seems to happen when you save then reopen the file, not if you just keep working in the file without closing it.

The macros behind the boxes are still there and run once I recreate the missing box (normally with an Alt-D, and editing the properties; but I've also created from scratch from the toolbar, and used alt-C, alt-V) they work fine. I've run a compile, with no change. Any thoughts on finding the root cause? I thought it might be from blowing up a macro, but I've tried it without even editing a macro and it has the issue.

View Replies!   View Related
Add Downloaded ActiveX Control To Toolbox
Version: excel 2003
OS: Win XP

Currently using VBA do develop a tool for Excel. I've downloaded a .ocx control and am wondering how one would go about adding it to the toolbox?

View Replies!   View Related
Copy And Paste To Worksheet Using Combobox (ActiveX)
I managed to put the names of my other worksheets into some comboboxes in the main worksheet by using this ....

View Replies!   View Related
ActiveX Click Control--STOPS When On Another Sheet
RE: Macro ActiveX Click Control--It stops when on another sheet

Macro runs OK when run from menu.

An activeX click button was put on sheet B.

Right click the control, View Code, Paste in code from macro, then click button.

It runs the part of the macro that applies to sheet B, goes to previous sheet A, and then stops. The range select line for the named range on sheet A is highlighted in yellow. It stops when selecting any range or cell on sheet A, like B2.

Any idea why this code does not work on sheet A when ActiveX button is on sheet B? Is there someplace else that the control can go? Using Excel 2000.

See part of code below, which starts on sheet B:

' Macro recorded 11/22/2007

Range("SortAll").Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Previous.Select
Range("FormBase").Select (STOPS HERE)
Selection.Copy
Range("Formul").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
FormBase is a named dynamic range
FormBase=OFFSET(A!$A$2,0,0,1,MATCH(1E+306,A!$2:$2))

View Replies!   View Related
Text Inconsistencies In ActiveX Combo Boxes
I'm building an application that uses a large number of activex combo boxes. These boxes are getting loaded from specific fields in an associated database.

I know ActiveX combo boxes can be buggy; I'm wondering if anyone has found a way around the text appearance inconsistencies seen in these controls? e.g., sometimes the text is smaller, sometimes it's spaced wider, sometimes it appears at the top of the combo box sometimes in the center.

I put a screen shot of what I'm talking about in a word doc along with a control properties screen shot, All controls have the same properties except for the text property.

View Replies!   View Related
OleObject ActiveX ComboBox Event Procedures
I've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.

Loopcntr = 1

Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)

'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)

Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1
Loop

View Replies!   View Related
Customize ActiveX SpinButton Value - Not Decrementing (or Incrementing) Correctly
I have an Active X SpinButton control directly on a spreadsheet that changes the input cell. (The idea is that later on, there'll be a bunch of these input cells, accompanied by their respective SpinButton controls).

I'm allowing the user to define the Min, Max, and Step (SmallChange) value of the SpinButton because (1) I need it to do decimal points and negative numbers and (2) that's what my boss would have wanted.

So I set the Min, Max, SmallChange value in the SpinButton properties to "0" then try to manipulate them in the VBA code.

What went wrong with the code is hard to explain. But basically, when I use the SpinButton to increment or decrement, it didn't quite do it correctly. If I fix the increment part of code, then the decrement part of the code goes wrong and vice versa.

An example: I have Min as "0.5%" in cell B7; Max as "4.5%" in cell B8; and Step (SmallChange) as "0.5%" in cell B9. The Input cell is in cell B10 where user can either type in a value within the defined range (enforced by data validation) or use the SpinButton to change the value.

To replicate the problem: I type in "1.0" in the Input cell and click on the left SpinButton and it would not allow me to go to the defined lower limit (which is 0.5%) in this case.

Here are my codes:

Private Sub SpinButton1_SpinUp()

Dim MyMax As Variant
Dim MyStep As Variant
Dim MyInput As Variant

MyMax = SpinButton1.Max + Range("B8").Value
MyStep = SpinButton1.SmallChange + Range("B9").Value

MyInput = Range("B10").Value...................................

View Replies!   View Related
Make Invisible Activex Command Button In Selection
I designed my spreadsheet incorrect.

Everything works good. But when I selecting tables with listbox, I forgot to notice that there also comboboxes.

I mean, rwgrietveld, I use selecting table to hide 'em, but when I hide 'em comboxes should also disappear (become invisible).

is it possible at all to make invisible activex commandbutton in selection?

View Replies!   View Related
Disable Or Stop ActiveX Control Events Firing
I've several check boxes on a worksheet. Some of the check boxes control the values of other through the _click event. However, when I try to change the value the event attached to the control in questions fires.

application.enableevents = False

would prevent events being fire from within the macro but this only seems to work when I step through the code.

I understand that I could set a global variable to do this and will probably go down this route anyway - but I am interested to know how/when I should be using the EnableEvent method (as I am sure this should be the way that I use it)

View Replies!   View Related
Return 2 Names Of ActiveX Worksheet Command Buttons
On a worksheet, I have several commandbuttons. For many of the buttons the name in the "name box" is different than the name in the "(name)" field under Properties.

Researching this, I found this on msdn2.microsoft.com:

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.

So, in my case, one button has:
In the name box: "CommandButton5"
In the (name) properties field: "RunProcess"

The real problem arises when I'm using code to loop through the objects on the worksheet, and build an array of the object names. I'm trying to match the name of the buttons to their click events. In the code, "btn.name" returns the name found in the "name box". The one that I want is the name in the "(name)" field under properties because this is the one that matches the click event.

How can I determine, in code, what is entered in the "(name)" field for an object?

View Replies!   View Related
Run-time Error '429' ActiveX Component Can't Create Object
I am trying to generate excel report output from access by click of a button. Part of the code is as follows:


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xlc As Object
Dim DBS As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

'If we do not want the first row we set Header to false
blnHeaderRow = False

'Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear

I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having
the following error in the line

Set xlx = GetObject(, "Excel.Application")

Run-time error '429'
ActiveX component can't create object

View Replies!   View Related
ActiveX Command Buttons - Dont Stick In The Same Place?
how to get some command buttons to stick in one place? I currently have two in a summary sheet. Each button runs a similar code which clears and deletes (by shifting cells upward) at the start of the process, then populates data.

It's rather annoying though that I cant have the buttons stick to one zone, they end up moving left or right depending on which button I press. Each report also changes column sizes, dont know if that's relevant?

View Replies!   View Related
Multiple Formats Inside Single Text Box (activex)
I'm having trouble keeping the formatting correctly in a text box through VBA. I'm sure this is something simple, but I'm the intern that got stuck with trying to program for the break since they know I've taken classes on the subject. I've been able to figure out a lot

End goal: To have the first word of a text box larger, underlined, and bolded. Problem: It only formats it Calibri at 24 point with no special formatting as indicated in the code.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved