How do I execute a macro based on the value of a cell in Excel?

[Guest]

For example, if cell A1=1, I want to run Macro1 once. If cell A1=2, I want
to run Macro2 once. If cell A1=3, do nothing.

I am familiar with Excel If/Then statements, so to my thinking, it would
look something like:

IF(A1=1,Run Macro1,IF(A1=2,Run Macro2,""))

I just don't know what the command is to "Run" the macro.

 

[Guest]

others will reply i am sure with code.However you need to decide wether the
macro runs automatically of a worksheet change event so every time cell a1 is
changed(or any cell for that matter ) the macro would run.Alternatively you
could have a button to run the macro or just from the menu tools>macro>run
macro

 

[Norman Jones]

Hi Brettop,

Try:
'===============>>
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1").Value
Case 1
macro1
Case 2
macro2
End Select
End If

End Sub
'<<===============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.
*******************************************

 

[Guest]

You need a worksheet change event macro. See:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

Thank you, Norman. This worked perfectly. It was exactly what I needed!

Brett

 

[Ltat42a]

Thank you, Norman. This worked perfectly. It was exactly what
needed!

Brett

I have a spreadsheet that I use an image to run a macro. If I were t
use the above script to run my macros, how do I do this. Where doe
this script go?
My choices for input would "A" - "B" or "C".

Than

 

[Norman Jones]

Hi Ltat42a,

I have a spreadsheet that I use an image to run a macro. If I were to
use the above script to run my macros, how do I do this. Where does
this script go?
My choices for input would "A" - "B" or "C".

Paste the following code into a standard module - not a worksheet module or
the ThisWorkbook module:

'===========>>
Sub aTester()
Select Case Range("A1").Value ' <<===== CHANGE
Case "A"
Macro1 '<<===== CHANGE
Case "B"
Macro2 '<<===== CHANGE
Case "C"
Macro3 '<<===== CHANGE
End Select
End Sub
'===========>>

Assign this macro to the image.

 

[Ltat42a]

Hi Ltat42a,


Paste the following code into a standard module - not a worksheet
module or
the ThisWorkbook module:

'===========>>
Sub aTester()
Select Case Range("A1").Value ' <<===== CHANGE
Case "A"
Macro1 '<<===== CHANGE
Case "B"
Macro2 '<<===== CHANGE
Case "C"
Macro3 '<<===== CHANGE
End Select
End Sub
'===========>>

Assign this macro to the image.

Oooppps. I should have explained better. I want to get rid of the
images I use to run the macros. When a user inserts an "A" into a
particular cell, it will run the "A" macro that I created, when someone
enters "B", it runs the "B" macro...etc...etc...


Sorry...

 

[Norman Jones]

Hi Ltat42a,

Oooppps. I should have explained better. I want to get rid of the
images I use to run the macros. When a user inserts an "A" into a
particular cell, it will run the "A" macro that I created, when someone
enters "B", it runs the "B" macro...etc...etc...

In that case, try:
'===============>>
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1").Value '<<=== CHANGE
Case "A"
Macro1
Case "B"
Macro2
Case "C"
Macro3
End Select
End If

End Sub
'<<===============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.
*******************************************


Change A1 to the cell of interest.

 

[Norman Jones]

Hi Ltat42a,

To avoid posible confusion

Change A1 to the cell of interest.

was intended to refer to both of the lines:

 

[Ltat42a]

To all who posted - this works much much better than clicking on a
image to execute a macro. Works great!

Thank you!!!


J

 

[Guest]

Norman-

I'm looking for a similar code as the one given in this example, however, I
want to reference 3 cell locations (A1, B1, C1) and based on their input run
a specific macro. Each cell has 3 options (1, 2, 3) and therefore having 18
different macros to run based on the combinations.

Do you have any suggestions? Right now I'm using a button to launch the
specific macro.

Thanks for your help!

Hi Ltat42a,

To avoid posible confusion

Change A1 to the cell of interest.

was intended to refer to both of the lines:

 

[Dave Peterson]

I'm not Norman.

But with 3 cells, I'd put a button on the worksheet that the user could click
when they had updated all three cells.

Then you could have a macro assigned to that button that determined the macro to
run.

Option Explicit
Sub testme()

Dim myStr As String
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1:A3")

If myRng.Cells.Count <> Application.CountA(myRng) Then
MsgBox "Please fill in all the cells"
Exit Sub
End If

myStr = .Range("a1").Value & .Range("a2").Value & .Range("A3").Value

If Len(myStr) > 3 Then
MsgBox "Check your entries!"
Exit Sub
End If

Select Case myStr
Case Is = "111": Call Macro111
Case Is = "112": Call Macro112
'keep typing--I'm bored
Case Is = "333": Call Macro333
Case Else:
MsgBox "Please fix your choices!"
Exit Sub
End Select
End With

End Sub

And wouldn't you have 27 different macros:
3 for the first choice * 3 for the second choice * 3 for the 3rd choice
????

Norman-

I'm looking for a similar code as the one given in this example, however, I
want to reference 3 cell locations (A1, B1, C1) and based on their input run
a specific macro. Each cell has 3 options (1, 2, 3) and therefore having 18
different macros to run based on the combinations.

Do you have any suggestions? Right now I'm using a button to launch the
specific macro.

Thanks for your help!