klopex
asked on
Combo Box in Excel
Hi, is it possible to have Combo Boxes in cells in Excel? If so how? (I commonly program in MS Access, but not much experience in Excel.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is what is in the formula box at the top when I click on a newly created combo box. My help will not show me anything about this object. What are the parameters to this function? How do I associate my combo boxes to certain actions that should occur?
=EMBED("Forms.ComboBox.1",
=EMBED("Forms.ComboBox.1",
Hi klopex,
Right click the ComboBox and select "View Code". There are several events in VBA (the Change event is the default) that you can then use to add code that performs actions based on user input or actions.
Once in VBA, on the top right of the Code pane, you can select which event you're attempting to trap from a dropdown list.
You can also use Data Validation in a similar fashion (but much less flexible) to a combo box as follows:
Select the target cell. From the menu select Data->Validation. In the dialogue box that appears, under "Allow:" select "List". Under "Source:", collapse the dialog box and point to the range of a previously entered list. Press Enter and then OK.
This will limit the users' entries to the list you've selected.
Hope this helps,
Justin
Right click the ComboBox and select "View Code". There are several events in VBA (the Change event is the default) that you can then use to add code that performs actions based on user input or actions.
Once in VBA, on the top right of the Code pane, you can select which event you're attempting to trap from a dropdown list.
You can also use Data Validation in a similar fashion (but much less flexible) to a combo box as follows:
Select the target cell. From the menu select Data->Validation. In the dialogue box that appears, under "Allow:" select "List". Under "Source:", collapse the dialog box and point to the range of a previously entered list. Press Enter and then OK.
This will limit the users' entries to the list you've selected.
Hope this helps,
Justin
klopex.... there are a few different types of "Combo Boxes" available for use in Excel.
Form combo boxes are the controls available from the "Forms" toolbox. (View > Toolbars > Forms)
Validation dropdown boxes are available through the Data > Validation menu option.
And ActiveX comboboxes available through the Control Toolbox. (View > Toolbars > Control Toolbox)
The ActiveX ComboBox is the most powerful of the three. It is very similar to the combo box that you use in Access. It has a properties tab, much like in Access, and it can respond to events, much like in Access.
Once you've added an ActiveX combobox to your Sheet, you can right click on it and select "properties". You'll see familiar properties such as "Name" and "Enabled" and "Visible", much like in Access. If you hit Alt+F11, you can open the VB Editor and select the module of the Sheet that you added the combo box to. You can then use the dropdown boxes to create events for that combobox.
There are a lot of things you can do to control an ActiveX combo box. Let me know of any specific needs you have, and I can help you further.
WATYF
Form combo boxes are the controls available from the "Forms" toolbox. (View > Toolbars > Forms)
Validation dropdown boxes are available through the Data > Validation menu option.
And ActiveX comboboxes available through the Control Toolbox. (View > Toolbars > Control Toolbox)
The ActiveX ComboBox is the most powerful of the three. It is very similar to the combo box that you use in Access. It has a properties tab, much like in Access, and it can respond to events, much like in Access.
Once you've added an ActiveX combobox to your Sheet, you can right click on it and select "properties". You'll see familiar properties such as "Name" and "Enabled" and "Visible", much like in Access. If you hit Alt+F11, you can open the VB Editor and select the module of the Sheet that you added the combo box to. You can then use the dropdown boxes to create events for that combobox.
There are a lot of things you can do to control an ActiveX combo box. Let me know of any specific needs you have, and I can help you further.
WATYF
Sorry Justin... I was posting in a rush yesterday... you must have been posting your response as I was typing mine.
WATYF
WATYF
No prob, WATYF, I'm a repeat offender at not "reloading the question" myself. Besides, you added some things that the questioner will probably find useful as well.
Best,
Justin
Best,
Justin
Hi
I'm doing the exact same thing with a combo box in ActiveX Combobox in Excel. I can get it to appear in the cells I want with the correct data populated however the user still has to click on the combobox in order to use it after it appears. I can't seem to use the SetFocus method for the combobox, it generates an error saying the control does not have the method. Any ideas? I know I should probably start a new question but you guys already seem to know what's going on with this type of functionality.
Thanks
rthomsen
I'm doing the exact same thing with a combo box in ActiveX Combobox in Excel. I can get it to appear in the cells I want with the correct data populated however the user still has to click on the combobox in order to use it after it appears. I can't seem to use the SetFocus method for the combobox, it generates an error saying the control does not have the method. Any ideas? I know I should probably start a new question but you guys already seem to know what's going on with this type of functionality.
Thanks
rthomsen
That's cause it's not caused "SetFocus"... it's called "Activate".
(and yes... we usually frown on question hijacking. :oP)
WATYF
(and yes... we usually frown on question hijacking. :oP)
WATYF
Thanks! Knew there had to be a method for it. I'm also more accustomed to Access.
Thanks for the grade, klopex! Glad I could be of some help.
Related question:
I would like to let a ComboBox get filled from an existing list.
How do I do that?
Do I have to do it programmatically?
What is this line all about? =EMBED("Forms.ComboBox.1";
Eman
I would like to let a ComboBox get filled from an existing list.
How do I do that?
Do I have to do it programmatically?
What is this line all about? =EMBED("Forms.ComboBox.1";
Eman
Hi Eman,
The accepted way to pose a new question is to create your own. Click on the "ask a question" link, type in an appropriate title and question and post it using the Submit button.
If you're concerned about points, click on the "take a survey" link, fill in the blanks, and you get 200 points, in addition to your original 20.
In the meantime, this thread discusses one option..
https://www.experts-exchange.com/questions/20749368/row-source-for-combo-box-SOS.html
There are others as well. I'll be looking forward to your post!
Thanks,
Justin
The accepted way to pose a new question is to create your own. Click on the "ask a question" link, type in an appropriate title and question and post it using the Submit button.
If you're concerned about points, click on the "take a survey" link, fill in the blanks, and you get 200 points, in addition to your original 20.
In the meantime, this thread discusses one option..
https://www.experts-exchange.com/questions/20749368/row-source-for-combo-box-SOS.html
There are others as well. I'll be looking forward to your post!
Thanks,
Justin
ASKER