How do I run a macro when cell value changes? WorksheetChange?
 

I want to trigger a macro whenever the user enters cell H11 and enters a
number 1-12 (not from a drop-down list though). The macro will take the
values in Cells M20, M24, M28, M32,M36, and copy them into different cells
(N10-14) on Sheet2, print them out and return to Sheet1, then clear the
original cells. Depending on whether the number entered was 1, 2..or 12, the
macro would go to a different sheet to print.
I can get this basic copy-print-clear process to run through a
recorded macro that runs off a button, but what I really want is for macro to
be triggerered by whichever number was entered into H11. I'm pretty the
answer starts with WorksheetChange, but I'm outta my league.

How do I run a macro when cell value changes? WorksheetChange?
 

Hi,


Something along these lines ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$11" Then
Application.EnableEvents = False
If Target.Value = 12 Then
place your code to copy
End If
Application.EnableEvents = True
End If
End Sub

HTH
Cheers
Carim

How do I run a macro when cell value changes? WorksheetChange?
 

This checks if the cell H11 has changed, the select case controls which
macro is run depending on the value 1 to 12. The code for value 3 to 12
passes a parameter to the sub (macro3) which can be used to control the
procedure as required.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 11 And Target.Column = 8 Then
Application.EnableEvents = False
Select Case Target.Value
Case Is = 1: Call Macro1
Case Is = 2: Call Macro2
Case 3 To 12: Call Macro3(Target.Value)
'etc....
End Select
Application.EnableEvents = True
End If
End Sub

Sub Macro1()
MsgBox "Call One"
End Sub

Sub Macro2()
MsgBox "Call Two"
End Sub

Sub Macro3(myValue As Integer)
MsgBox "Value entered: " & myValue
End Sub

--
Cheers
Nigel



"kvnexcel" wrote in message
...
I want to trigger a macro whenever the user enters cell H11 and enters
a
number 1-12 (not from a drop-down list though). The macro will take the
values in Cells M20, M24, M28, M32,M36, and copy them into different cells
(N10-14) on Sheet2, print them out and return to Sheet1, then clear the
original cells. Depending on whether the number entered was 1, 2..or 12,
the
macro would go to a different sheet to print.
I can get this basic copy-print-clear process to run through a
recorded macro that runs off a button, but what I really want is for macro
to
be triggerered by whichever number was entered into H11. I'm pretty the
answer starts with WorksheetChange, but I'm outta my league.

How do I run a macro when cell value changes? WorksheetChange?
 

THANKS CARIM. WILL GIVE IT A TRY and let you know how it works out.
Appreciate your assistance.

"Carim" wrote:

Hi,


Something along these lines ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$11" Then
Application.EnableEvents = False
If Target.Value = 12 Then
place your code to copy
End If
Application.EnableEvents = True
End If
End Sub

HTH
Cheers
Carim

How do I run a macro when cell value changes? WorksheetChange?
 

NIGEL -- Thank you. I will give this a try and let you know how it turns
out. I can follow this logic easily. I appreciate your help.

"Nigel" wrote:

This checks if the cell H11 has changed, the select case controls which
macro is run depending on the value 1 to 12. The code for value 3 to 12
passes a parameter to the sub (macro3) which can be used to control the
procedure as required.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 11 And Target.Column = 8 Then
Application.EnableEvents = False
Select Case Target.Value
Case Is = 1: Call Macro1
Case Is = 2: Call Macro2
Case 3 To 12: Call Macro3(Target.Value)
'etc....
End Select
Application.EnableEvents = True
End If
End Sub

Sub Macro1()
MsgBox "Call One"
End Sub

Sub Macro2()
MsgBox "Call Two"
End Sub

Sub Macro3(myValue As Integer)
MsgBox "Value entered: " & myValue
End Sub

--
Cheers
Nigel



"kvnexcel" wrote in message
...
I want to trigger a macro whenever the user enters cell H11 and enters
a
number 1-12 (not from a drop-down list though). The macro will take the
values in Cells M20, M24, M28, M32,M36, and copy them into different cells
(N10-14) on Sheet2, print them out and return to Sheet1, then clear the
original cells. Depending on whether the number entered was 1, 2..or 12,
the
macro would go to a different sheet to print.
I can get this basic copy-print-clear process to run through a
recorded macro that runs off a button, but what I really want is for macro
to
be triggerered by whichever number was entered into H11. I'm pretty the
answer starts with WorksheetChange, but I'm outta my league.