user defines macro to run

[Guest]

I have a tool were I would like to let a user key in a number that
corresponds to a certain macro, and only that macro will run. For example,
this tool has about 40 different macros that can run in succession with the
click of a button (this will take well over 30 minutes to complete), but
sometimes the user may want to only run one of the macros. Each macro is
named as follows: Run0001, Run 0002, Run0003, ... , Run0040. I was hoping
to have a cell or a text box where the user types in the particular macro
they want to run (0001, or 0002, or 0040).
Any help on this topic would be awesome.
Thanks

 

[Guest]

Use an inputbox to get a number and then a CASE statement to run the selected
one

 

[Guest]

I've got the number now from an input box, but how do I get excel to use that
number when running the macro? (I dont think I'm familiar with a how to use
"a CASE statement to run the selected one")
Thanks

 

[Dave Peterson]

Option Explicit
sub testme()

dim myNum as long
mynum = clng(application.inputbox(Prompt:="what one?", type:=1))
if mynum < 1 _
or mynum > 40 then
msgbox "ok. Quitting"
exit sub
end if

application.run "'" & thisworkbook.name & "'!Run" & format(mynum,"0000")
end sub

But as a user, I'd have a difficult time remembering what Run0013 does compared
to what run0039 does.

You may want to consider adding a toolbar or items to the worksheet menubar that
has some descriptions -- instead of just numbers.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Here is an example, just in CASE:

Sub bwikl()
i = Application.InputBox("enter the secret code:", Type:=1)
Select Case i
Case 1
Call macro1
Case 2
Call macro2
Case 3
Call macro3
End Select
End Sub
Sub macro1()
MsgBox ("1")
End Sub
Sub macro2()
MsgBox ("2")
End Sub
Sub macro3()
MsgBox ("3")
End Sub