assigning macro to cell

[Guest]

Is it possible to run a macro depending on the input in a cell?
I would like to run:
Macro 1 if I input 2005 in cell b4
Macro 2 if I input 2006 in cell b4
Macro 3 if I input 2007 in cell b4

Thanks

 

[Guest]

Adam,
Add this code to the sheet which contains the B4 cell . In VBA
project window, right click on (say) Sheet1, click View Code and copy this
code into the sheet.


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target = Range("$b$4") Then
With Target
Select Case .Value ' Assumes cell format is General
Case Is = 2005
Call Macro1
Case Is = 2006
Call Macro2
Case Is = 2007
Call Macro3
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub



HTH

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case .Value
Case 2005: Macro1
Case 2006: Macro2
Case 2007: Macro3
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Your IF statement says

IF Target.Value = Range("$b$4").Value

so any cell having the same value as Cell B4 (including B4) will execute the
case statement. I doubt that is what your intent is (and it isn't the
stated intent of the OP).

Try

If Target.Address = "$B$4" Then

Instead

 

[Tom Ogilvy]

I am sure Bob meant

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
If Target.Address = "$B$4" Then
Application.EnableEvents = False
With Target
Select Case .Value
Case 2005: Macro1
Case 2006: Macro2
Case 2007: Macro3
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Indeed, thanks Tom.

Bob

I am sure Bob meant

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
If Target.Address = "$B$4" Then
Application.EnableEvents = False
With Target
Select Case .Value
Case 2005: Macro1
Case 2006: Macro2
Case 2007: Macro3
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks Tom. .. Too much of a hurry!

Your IF statement says

IF Target.Value = Range("$b$4").Value

so any cell having the same value as Cell B4 (including B4) will execute the
case statement. I doubt that is what your intent is (and it isn't the
stated intent of the OP).

Try

If Target.Address = "$B$4" Then

Instead