Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
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. |
#2
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
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 toolsmacrorun macro -- paul remove nospam for email addy! "brettopp" wrote: 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. |
#3
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
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. ******************************************* --- Regards, Norman "brettopp" wrote in message ... 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. |
#4
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
You need a worksheet change event macro. See:
http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student "brettopp" wrote: 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. |
#5
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel
Thank you, Norman. This worked perfectly. It was exactly what I needed!
Brett "Norman Jones" wrote: 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. ******************************************* --- Regards, Norman "brettopp" wrote in message ... 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. |
#6
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
brettopp Wrote: Thank you, Norman. This worked perfectly. It was exactly what I needed! Brett "Norman Jones" wrote: 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. ******************************************* --- Regards, Norman "brettopp" wrote in message ... 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. 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". Thanx -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=478509 |
#7
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
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. --- Regards, Norman |
#8
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
Norman Jones Wrote: 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. --- Regards, Norman 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... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=478509 |
#9
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
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. --- Regards, Norman "Ltat42a" wrote in message ... Norman Jones Wrote: 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. --- Regards, Norman 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... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=478509 |
#10
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
Hi Ltat42a,
To avoid posible confusion Change A1 to the cell of interest. was intended to refer to both of the lines: If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value '<<=== CHANGE --- Regards, Norman "Norman Jones" wrote in message ... 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. --- Regards, Norman |
#11
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel?
To all who posted - this works much much better than clicking on an image to execute a macro. Works great! Thank you!!! JF -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=478509 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel
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! "Norman Jones" wrote: Hi Ltat42a, To avoid posible confusion Change A1 to the cell of interest. was intended to refer to both of the lines: If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value '<<=== CHANGE --- Regards, Norman "Norman Jones" wrote in message ... 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. --- Regards, Norman |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I execute a macro based on the value of a cell in Excel
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 ???? jkt wrote: 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! "Norman Jones" wrote: Hi Ltat42a, To avoid posible confusion Change A1 to the cell of interest. was intended to refer to both of the lines: If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value '<<=== CHANGE --- Regards, Norman "Norman Jones" wrote in message ... 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. --- Regards, Norman -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change fill color in excel based on data in the cell | Excel Worksheet Functions | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
enter a new value into a cell using macro in excel | Excel Discussion (Misc queries) | |||
Excel formulas based upon the color shading of a cell? | Excel Worksheet Functions |