Creating a combobox combined with macro in Excel 2000

[danground]

This is too hard for me so i'll ask you and we.ll see if anyone can help me. I want to create a combobox with the values 00-99 which is linked to several, actually, a hundred macros that i've created. How do i make this work? I suck at VB so someone's got to help me out!

And by the way, for me that's using a swedish version, can aqnyone explain what the sheet is. Is it the curretn file, or "book" your working on, or is it the currrent page?

Take care!

/Dan

 

[Andrew Poulsom]

In row 1 of a spare column enter this formula:

=TEXT(ROW(A1)-ROW(A$1),"00")

and copy it to rows 2 to 100. Name that range eg List using Insert|Name|Define.

Right click any Toolbar and choose Control Toolbox. Click the ComboBox icon and click and drag in your worksheet to position and size it. Right click the ComboBox and choose Properties. Against the ListFillRange property type List and close the Properties window. Right click the ComboBox and choose View Code. Paste this into the window on the right (overwriting what's already there):

Private Sub ComboBox1_Change()
    Application.Run "Macro" & ComboBox1.Value
End Sub

The code assumes that your macros are named Macro00 thru Macro99. Adjust to suit.

Press Alt+F11 to return to your worksheet and click the Design icon at top left of the Control Toolbox to exit design mode.

Try it out by selecting an item from the ComboBox.

A workbook is a file on disk, eg Book1.xls. A worksheet is a page within a workbook, eg Sheet1.