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

Use an inputbox to get a number and then a CASE statement to run the selected
one
--
Gary''s Student
gsnu200711

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

"Gary''s Student" wrote:

Use an inputbox to get a number and then a CASE statement to run the selected
one
--
Gary''s Student
gsnu200711

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)


bwilk77 wrote:

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

--

Dave Peterson

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

--
Gary''s Student
gsnu200711