macro with input msg based on cell contents
 

I have previously posted this question and have not received a response to
date - please can someone help me....:)
I need help to run a macro with input msg when a date is entered into the
cell eg. date is input to cell M20 - input message "Budget received - do you
want to complete Budget Calculator" If "Yes" is selected then worksheet
"Budget Calc" is copied and renamed based on contents of cell A2 (I think I
have the renaming sorted thanks to previous posts). If "No" selected then
macro stops.
Any help would be appreciated.

macro with input msg based on cell contents
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "M20" '<== change to suit
Const msg As String = "Budget received - complete the Budget Calculator"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If MsgBox(msg, vbYesNo) = vbYes Then

Worksheets("Budget Calc").Copy _
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Me.Range("A2").Value
Me.Activate
End If
End With
End If

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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Janelle S" wrote in message
...
I have previously posted this question and have not received a response to
date - please can someone help me....:)
I need help to run a macro with input msg when a date is entered into the
cell eg. date is input to cell M20 - input message "Budget received - do
you
want to complete Budget Calculator" If "Yes" is selected then worksheet
"Budget Calc" is copied and renamed based on contents of cell A2 (I think
I
have the renaming sorted thanks to previous posts). If "No" selected then
macro stops.
Any help would be appreciated.

macro with input msg based on cell contents
 

Thank you so much, Bob. This has worked perfectly - I really appreciate you
taking the time to look at this. Communities is the best thing ever.

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "M20" '<== change to suit
Const msg As String = "Budget received - complete the Budget Calculator"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If MsgBox(msg, vbYesNo) = vbYes Then

Worksheets("Budget Calc").Copy _
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Me.Range("A2").Value
Me.Activate
End If
End With
End If

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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Janelle S" wrote in message
...
I have previously posted this question and have not received a response to
date - please can someone help me....:)
I need help to run a macro with input msg when a date is entered into the
cell eg. date is input to cell M20 - input message "Budget received - do
you
want to complete Budget Calculator" If "Yes" is selected then worksheet
"Budget Calc" is copied and renamed based on contents of cell A2 (I think
I
have the renaming sorted thanks to previous posts). If "No" selected then
macro stops.
Any help would be appreciated.