Solved - Call Macro Based On Cell Value

LearningVBA

New Member
Joined
Sep 16, 2006
Messages
14
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 16, 2006
Messages
14
Not quiet what i'm after. I was thinking on the lines of

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi

Try this:

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


Best regards

Richard
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Sep 16, 2006
Messages
14

ADVERTISEMENT

Richard,

Your code stops at

Code:
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

Active Member
Joined
Jun 6, 2005
Messages
326
Maybe this

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




Richard,

Your code stops at

Code:
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

New Member
Joined
Sep 16, 2006
Messages
14
I had the macro in the worksheet part, moved it to a module and its now working.

Thanks everyone
 

Forum statistics

Threads
1,141,681
Messages
5,707,795
Members
421,528
Latest member
datdude151

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top