Prev: Left Function
Next: PROTECTING CELLS
From: Malaria Man Malaria on 20 Apr 2010 12:58
I am trying to have 1 of 8 different macro's run depending on the value of a
specific cell.
If I enter 1 in cell A3, I would like macro1 to run. If I change cell A3 to
2, I would like macro2 to run and so on all the way up to entering 8 and
getting macro8 to run. I am unable to find any good examples of how to do
this. I realize I will probably have to use Worksheet Event to do this but
what is the macro code I will need.
From: Jim Thomlinson on 20 Apr 2010 13:06
Right click the sheet tab and select veiw code. Paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$3" Then
Select Case .Value
Case "A" 'Change A
Call macro1
Case "B" 'Change B
Call macro2
End Select

End If
End With
End Sub

Chang ethe A and B to the values that you want to trigger the macros. Add as
many different cases as you need.
--
HTH...

Jim Thomlinson


"Malaria Man" wrote:

> I am trying to have 1 of 8 different macro's run depending on the value of a
> specific cell.
> If I enter 1 in cell A3, I would like macro1 to run. If I change cell A3 to
> 2, I would like macro2 to run and so on all the way up to entering 8 and
> getting macro8 to run. I am unable to find any good examples of how to do
> this. I realize I will probably have to use Worksheet Event to do this but
> what is the macro code I will need.
From: Paul C on 20 Apr 2010 13:10
Create a Worksheet Change macro and check the target position and value like
this.

If any cell but A3 is changed nothing get executed


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row=3 and Target.Column=1 then
Select Case target.value
Case 1
Call macro1
Case 2
Call macro2
etc...
End Select
End If

End Sub


--
If this helps, please remember to click yes.


"Malaria Man" wrote:

> I am trying to have 1 of 8 different macro's run depending on the value of a
> specific cell.
> If I enter 1 in cell A3, I would like macro1 to run. If I change cell A3 to
> 2, I would like macro2 to run and so on all the way up to entering 8 and
> getting macro8 to run. I am unable to find any good examples of how to do
> this. I realize I will probably have to use Worksheet Event to do this but
> what is the macro code I will need.
From: Don Guillett on 20 Apr 2010 13:10
Ease of design depends on the actual names of the macros. Assuming they are
NOT really macro1 , macro2, etc then I suggest a select case macro something
like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("a3").Address Then Exit Sub
Select Case Target
Case Is = 1: Call macro1
Case Is = 2: Call macro2
Case Else: MsgBox "no such"
End Select
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Malaria Man" <Malaria Man(a)discussions.microsoft.com> wrote in message
news:3531F269-73E5-4427-972B-1238EF6EF992(a)microsoft.com...
>I am trying to have 1 of 8 different macro's run depending on the value of
>a
> specific cell.
> If I enter 1 in cell A3, I would like macro1 to run. If I change cell A3
> to
> 2, I would like macro2 to run and so on all the way up to entering 8 and
> getting macro8 to run. I am unable to find any good examples of how to do
> this. I realize I will probably have to use Worksheet Event to do this but
> what is the macro code I will need.

 | 
Pages: 1
Prev: Left Function
Next: PROTECTING CELLS