Attaching Macros to Form ComboBox Control
Posted by Clinton Hayes on August 29, 2001 4:03 AM
Hi...I hope someone can help.
I'm just learning the difference between ComboBoxes as a Forms control and as a VBA (Active X) control. Whilst the forms control gives me almost everything I need, as I don't want the ComboBox to print under certain conditions, I have to write some code to address this.
If I use a Forms control, I need to attach a Macro to run the code and I'm having trouble with this. If I use a VBA control, it's not as simple for me as it doesn't provide me with Input range or the cell link and I'm not sure how to add my data etc.
When I try to run the Macro for the Forms control, it gives me a 424 runtime error saying that an object is required. The code I'm trying to run is:
If DropDown26 = "N/A" Then
DropDown26.PrintObject = False
Else
DropDown.PrintObject = True
End If
I call it a Drop Down 'cause that's what Excel calls it but I've tried ComboBox and I get the same result. This is probably pretty simple to someone who know but I'm stuck.
Anybody want to assist?
Thanks,
Clinton.
Posted by RobJackson on August 29, 2001 6:20 AM
OK, you need to use active X controls. Your code is fine, just follow this procedure.
Put the control on the sheet, on the menu for X controls you will see a kind of right angled triangle button, this must be depressed for you to be able to work on the control.
Then Right Hand click on the control and you will see another menu, select 'view code', the screen will change to the VBA environment. (ALT+F11 toggles between Excel and VBA).Put your code between the Sub... line and the End Sub line and change the DropDown name in the code to match that in the Sub... Line.
As far as the linked cell goes, the active X controls will place the word/number selected not the list index number. If you want this just add the line before your IF statement.
Range("'Sheetname'!F7").value=combobox.listindex
where combobox is the name of your activeX control. (note the List Index on an Active X starts at 0 and on a Form it starts at 1) IF you're happy with the word then just RH click on the control and select properties. Find the one labled Linked Cell in the window that appears and set accordingly (you will need to type the reference in the form A1 orif you have set the range name then just type that). Do the same with the property ListFillRange for the list of words.
(There is also a MatchRequired property you may find useful).
Don't forget to deselect the little RightAngled Triangle button to take it off edit mode.
Good Luck
Rob
Posted by Clinton Hayes on August 29, 2001 4:02 PM
Rob,
Thank you so much for taking the time. That worked fine.
By the way, I have to open the sheet with Macros Disabled to do the editing and close it again and reopen with Macros Enabled to test.
I don't suppose there's a way to Enable and Disable Macros without opening and closing the spreadsheet?
All the best and thanks again.
Cheers,
Clinton.
Posted by Rob Jackson on August 30, 2001 1:54 AM
No way I know of to enable/disable the macros although you should be able to enter the edit mode with macros enabled.
But at least it worked for you.
Good Luck...
