Run or Call a sub from the list of subs in a drop down.

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

If I have a list of ten sub names in a drop down F1, can I make my MySubCaller run the sub I choose in F1?

This little diddy doesn't work and the scant info I can find to do this leads me to think it is a bogus way to manage the running of ten subs from a single on sheet button.

Thanks,
Howard

Option Explicit

Sub MySubCaller()
Dim i As String
i = Range("F1").Value
Application.Run i
End Sub
 
If I have a list of ten sub names in a drop down F1, can I make my MySubCaller run the sub I choose in F1?



This little diddy doesn't work and the scant info I can find to do this leads me to think it is a bogus way to manage the running of ten subs from a single on sheet button.



Thanks,

Howard



Option Explicit



Sub MySubCaller()

Dim i As String

i = Range("F1").Value

Application.Run i

End Sub
Click to expand...

I found this but I still get an error 400 with:

Application.Run (i)


expression .Run(Macro,[Arg1, , , Arg30])
expression A variable that represents an Application object

Howard
 
Hi Howard,

Am Sun, 29 Sep 2013 23:14:45 -0700 (PDT) schrieb Howard:
Application.Run (i)
Click to expand...

try worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub

Application.Run (Target)
End Sub


Regards
Claus B.
 
Hi Howard,



Am Sun, 29 Sep 2013 23:14:45 -0700 (PDT) schrieb Howard:






try worksheet_change event:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$F$1" Then Exit Sub



Application.Run (Target)

End Sub





Regards

Claus B.
Click to expand...

With this in the sheet 1 module I get an error on the App.Run(Target) line
saying macros may not be available or may be disabled.
(There are three more macros like the one shown)

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub
Application.Run (Target)
End Sub

Sub xx1()
MsgBox "one"
End Sub
 
Hi Howard,

Am Mon, 30 Sep 2013 01:11:37 -0700 (PDT) schrieb Howard:
Sub xx1()
Click to expand...

xx1 is a cell reference. You can't name a macro like a cell reference


Regards
Claus B.
 
Hi Howard,

Am Mon, 30 Sep 2013 10:35:40 +0200 schrieb Claus Busch:
xx1 is a cell reference. You can't name a macro like a cell reference
Click to expand...

a macro named like a cell reference you can run from VBA editor.
If you try to run it from a sheet you can't, because the Run button is
disabled.


Regards
Claus B.
 
Hi Howard,



Am Mon, 30 Sep 2013 10:35:40 +0200 schrieb Claus Busch:






a macro named like a cell reference you can run from VBA editor.

If you try to run it from a sheet you can't, because the Run button is

disabled.





Regards

Claus B.
Click to expand...

Boy do I feel stupid. In my haste I just wanted to create four or five macros to set up my code. Completely over looked the cell ref's I was using.

Thanks Claus.

Regards,
Howard
Howard
 
Boy do I feel stupid. In my haste I just wanted to create four or
five macros to set up my code. Completely over looked the cell ref's
I was using.

Thanks Claus.

Regards,
Howard
Howard
Click to expand...

Test1()
Test2()
Test3()
Test4()
Test5()

OR as does the macro recorder...

Macro1()
Macro2()
Macro3()
Macro4()
Macro5()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top