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.
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
--
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.
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
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.
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>
Originally Posted by brettopp
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
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...
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
To all who posted - this works much much better than clicking on an image to execute a macro. Works great!
Thank you!!!
JF
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks