Getting to grips with Combo boxes

Martin Perera

Board Regular
Joined
Jan 17, 2005
Messages
147
ok guys
:)
I am trying to get to grips with a combo box. heres a simple senario probably with a simple solution........ :confused:

It is created from the form toolbar.

it is very basic, it literally list the months of the year, Jan to feb from a range on the same sheet.
what I am trying to do is set each option(month)to run a sperate macro ie
  • Jan -- GoTo K10
    Feb GoTo k14 etc
.
I am making this as simple as possible just to get the hang of the procedure.

the question is HOW do I get the combo box to recognise more than one macro?
or
will it only recognise one macro and therefore that macro needs to somehow select the right macro to run? via some sort of case

cheers for any ideas

martin
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Assuming that all you have in the combobox is Jan through to Dec then you could assign the follwoing "main" macro to your combobox which uses the "Select Case" to run code based upon what value was chosen. You could chage the "Msgbox "Jan"" lines to do something more interesting like to call another subroutine, but I just want to show you how the Select Case works.

In general though, the "Form" toolbar is only provided for backwards compatibility for versions of Excel prior to Excel 97. That's why we're having the use the crappy .Value property and 1 to 12 to decipher which month was chosen in the Select Case. You might find it easier and more productive to try using similar code using the combobox found on the "Control Toolbox". These controls look slightly better too.

Code:
Public Sub main()
  
  Select Case Worksheets("Sheet1").DropDowns("Drop Down 1").Value

  Case 1
    MsgBox "Jan"
  Case 2
    MsgBox "Feb"
  Case 3
    MsgBox "Mar"
  Case 4
  
  Case 5

  Case 6
  
  Case 7
  
  Case 8
  
  Case 9
  
  Case 10
  
  Case 11
  
  Case 12
  
  Case Else
  
  End Select
End Sub
 
Upvote 0
many thanks Mark
Code:
Select Case Worksheets("Sheet1").DropDowns("Drop Down 1").Value
I assume the .Dropdowns refers to the combo boxs and therefore ("Drop Down 1"). and ("Drop Down 2"). would be used if there were more than 1 combo boxes in the sheet.

You might find it easier and more productive to try using similar code using the combobox found on the "Control Toolbox".

so where would I find the"Control Toolbox" and how do I set it up, I assume it is something to do with "Userforms" rather than the "Forms" toolbar?

:cool: martin
 
Upvote 0
You wont find "Dropdowns" in the help file or at least, it will only tell you that it is a function that is no longer supported. (i.e. documented) It does refer to the combobox that you are using, but you would need to change the name of the worksheet and "Drop Box 1" to whatever worksheet you are using and to whatever the name of the combobox on that worksheet is.

The "Control Toolbox" toolbar is not anything to do with userforms but is found in the same place as the "Forms" toolbar that you have already used. i.e. View>>Toolbars>>Control Toolbox
 
Upvote 0
Im sorry but I am utterly clueless. Have read tons of threads on combo boxes but cannot seem to get it to work. have tried the code posted above, which is the code martin passed on to me, but dont know how to adapt it to what I need. Get error message that Cannot find the macro, although im pretty sure I assigned one. Do i paste the code in Worksheet space, or as a module? How do I adapt the code so that when the first choice in the combo box is chosen, the word "plans" appears in the selected cell.

I am working in a swedish excel, but does that affect VBA? Is "Sheet1", the actualy name of the sheet, which is Colours, or do i leave it as Sheet 1, which is the sheet where i want the combo box to operate.... please someone help...

Please be gentle. youre dealing with a frustrated, desperate newbie
 
Upvote 0
Hi there Littleme :)

fun things these combo's aint they lol
from what I gather, "sheet1" would change to "Colours",
I put it in the worksheet space not modules or workbook.
To paste a word into a selected cell on the same sheet ("Colours") try this
In Module1

Code:
sub addPlans()

Range("H9").Value = "Plans"

end sub

then in the main()

Code:
Public Sub main() 
  
  Select Case Worksheets("Colours").DropDowns("Drop Down 1").Value 

  Case 1 
    Module1.addPlans 

'other cases here
'
'
Case Else 
  
  End Select 
End Sub

I think that should work, unless you encounter the problem I am having. Which is when I put in my workbook sheet called "Navigate" but otherwise exactly as the above code. it returns a "400" in a message box, not even error 400 just 400. weird help menu doesnt help it comes up literally blank. If I set it up in another workbook it works.

What is this "400" it has a "microsoft visual basic " title in the box. with a red circle with a white cross in it, then the text 400.

I have no idea what is going on.............. :devilish:
 
Upvote 0
ok so i cannot figure out the error 400 thing so.....

I have created a combo box from the control toolbox instead in the hope that it will solve the problem for me.

i have got the box, done the fillrange bit, but how do I get the equivalent of the change case code for this type of combo box, as if i try and use it i get an error object doesnt use this property????

yours in confusion

martin
 
Upvote 0
I would be happy for that =)... keep getting error message that cannot find macro, or sometimes Compilation error. Expected: Listdividor or ).... (dont know if those are the english terms since im working in a swedish version... complicates things a bit....) if you have any ideas would be happy
 
Upvote 0
Thank you so much for all your help!!!! I could kiss you! Big hug to you nonetheless. I really appreciate it

For those of you who have followed and had the same problem i did, the difference between the code that was posted previously, and what Martin set me up with later, is that the line

select case worksheets ("sheet1").Dropdowns("drop down 1").value

Should apparantly be

Select Case Dropdowns.Value

Pastin in modules in the cases worked really well...

Thank you once again martin!!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top