Solved - Call Macro Based On Cell Value

[LearningVBA]

I've a command button which is set to run a macro, my problem is I need to add another 30 buttons to call various macro's.

Rather than do this, is there a way in which I can call the macro based on the contents of cell G3? This would make things alot easier and look better on the worksheet.

Thanks

 

[Joe4]

How is the value in G3 changed? Manually or is it a formula?

You can use VBA called Event Procedures, which is VBA that is automatically triggered upon some event happening.

If G3 is manually updated, you can use the Worksheet_Change event.
If G3 is changed by formula, you can use the Worksheet_Calculate event.

To read up on these Event Procedures and see how they work, check out this link:
http://www.cpearson.com/excel/events.htm

 

[Peter_SSs]

LearningVBA

Maybe something like this will suit you.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address <> "$G$3" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("G3").Value
        <SPAN style="color:#00007F">Case</SPAN> "a"
            <SPAN style="color:#007F00">'macro 1 here</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "b"
            <SPAN style="color:#007F00">'macro 2 here</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "c", "d"
            <SPAN style="color:#007F00">'macro 3 here</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#007F00">'possible macro 4 here</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[LearningVBA]

Not quiet what i'm after. I was thinking on the lines of

Private Sub CommandButton1_Click()

Range("A2").Select
Call Range("a2")
End Subcode]

But being new to this code stuff am struggling.  The value in cell A2 will be a formula.

 

[Richard Schollar]

Hi

Try this:

Private Sub CommandButton1_Click() 
Dim temp as String
temp = Range("A2").Value
Application.Run(temp) 
End Sub

Best regards

Richard

 

[Joe Was]

I would put an In-Cell-Data-Validation-DropDown-List in your Trigger cell on the sheet. Then the user can select the macro option from the dropdown list. If you name your Macros the the same as the items in the dropdown list then it would be a direct Call, but this is not actually necessary.


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet2, only!

If (Target.Address <> "$A$1" Or Target.Value = "") Then Exit Sub

Select Case Target.Value
Case "Test1"
Call Test1

Case "Test2"
Call Test2

Case "Test3"
Call Test3

Case Else
MsgBox "Error: Selected option not found!", vbCritical + vbOKOnly, "Option Error!"
End Select

myEnd:
Target.Value = ""
End Sub

Sub Test1()
MsgBox "The Sub: ""Test1"" was executed!"
End Sub

Sub Test2()
MsgBox "The Sub: ""Test2"" was executed!"
End Sub

Sub Test3()
MsgBox "The Sub: ""Test3"" was executed!"
End Sub

 

[LearningVBA]

Richard,

Your code stops at

Application.Run (temp)

I get the following error message

Run-time error '1004':
Application-defined or object-defined error.


Can you help further as I think its what I'm after?

 

[KenCriss]

Maybe this

Private Sub CommandButton1_Click()
Dim temp as String
temp = Range("G3").Value
Application.Run(temp)
End Sub

Richard,

Your code stops at

Application.Run (temp)

I get the following error message

Run-time error '1004':
Application-defined or object-defined error.


Can you help further as I think its what I'm after?

 

[LearningVBA]

I had the macro in the worksheet part, moved it to a module and its now working.

Thanks everyone