Assigning Macros to Combo Box list

E

eric

How do I assign separate macros to separate listings withing a combo drop
down box? I am trying to create a combo drop down box that takes you to a
specified sheet within the same workbook.
 
M

Myrna Larson

I expect you don't. I've never done this, but it would seem to me that you
read the value of the combo box and move to the correct sheet based on that
value, probably using a Select Case statement in the code.
 
S

SeaparkJohn

Assuming that the listings in the combobox match the names of the sheets do
this..

set up a table of the sheet names somewhere in the workbook. In the cell
next to each listing insert a hyperlink that takes you to the desired sheet.
Now write a macro connected to the combobox. In the macro, you take the
value of the combobox, you go to the first cell in the table you just made
and you do this

do until activecell.value = combobox.value
activecell.offset(1,0).select
loop

this will move down through the table until you have reached the cell
identical to the value selected form the combo box.

activecell.offset(1,0).select

this will select the cell next to it, which you will have already inserted
the hyperlink into. You will now hyperlink to the desired sheet.

If this was helpful, let it be known...
 
D

Dave Peterson

Did you put this combobox or Dropdown on a worksheet?

If yes, did you use a combobox from the Control toolbox toolbar?

If yes, you could use this code:

Option Explicit
Sub ddSelectSheets()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
On Error Resume Next
Worksheets(.List(.ListIndex)).Select
If Err.Number <> 0 Then
Beep
Err.Clear
End If
On Error GoTo 0
End With
End Sub

==
Just double click on the combobox and while you're in design mode and you'll be
taken to the location to paste this code.

======

If you used a dropdown from the Forms toolbar, you could assign the dropdown
this macro:

Option Explicit
Sub ddSelectSheets()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
On Error Resume Next
Worksheets(.List(.ListIndex)).Select
If Err.Number <> 0 Then
Beep
Err.Clear
End If
On Error GoTo 0
End With
End Sub


This kind of code goes into a General module.

hit alt-f11 to see the VBE.
select your workbook/project
rightclick on it and choose Insert|module
paste that second code in that code window.

Back to excel. Right click on the dropdown and select assign macro. Choose
ddSelectsheets and try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Another alternative:

How about a floating toolbar that displays all the worksheet names?

http://groups.google.com/[email protected]

You can use it for any open workbook.
 
Top