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.

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

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.

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

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.