Event Procedures: Event on Worksheet to fire Event on another Worksheet
 

The following code works fine if I change the value of the
target (D26) directly in the worksheet. However, I want
this event to be triggered by a user entering a date in
another worksheet (the target worksheet will not have
focus). Target is difference between TODAY() and date
entered by user so it will change when the user enters
date.

Douglas was asking about this on March 7 & 8 but I can't
find the solution. Any suggestions?

Thanks so much! You are all so helpful!
Kathryn

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D26")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp:
With Target
If .Value Then
Application.EnableEvents = False
Dim Msg, Style, Title, Help, Ctxt, Response,
MyString
Msg = "Do you want to continue ?" ' Define
message.
Style = vbYesNo + vbCritical +
vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define
title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help,
Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

Event Procedures: Event on Worksheet to fire Event on another Worksheet
 

Hi
you may have a look at the worksheet_calculate event and use this event
instead of your current worksheet_change event

--
Regards
Frank Kabel
Frankfurt, Germany

"Kathryn" schrieb im Newsbeitrag
...
The following code works fine if I change the value of the
target (D26) directly in the worksheet. However, I want
this event to be triggered by a user entering a date in
another worksheet (the target worksheet will not have
focus). Target is difference between TODAY() and date
entered by user so it will change when the user enters
date.

Douglas was asking about this on March 7 & 8 but I can't
find the solution. Any suggestions?

Thanks so much! You are all so helpful!
Kathryn

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D26")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp:
With Target
If .Value Then
Application.EnableEvents = False
Dim Msg, Style, Title, Help, Ctxt, Response,
MyString
Msg = "Do you want to continue ?" ' Define
message.
Style = vbYesNo + vbCritical +
vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define
title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help,
Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

Event Procedures: Event on Worksheet to fire Event on another Worksheet
 

Thank you!


-----Original Message-----
Hi
you may have a look at the worksheet_calculate event and
use this event
instead of your current worksheet_change event

--
Regards
Frank Kabel
Frankfurt, Germany

"Kathryn" schrieb
im Newsbeitrag
...
The following code works fine if I change the value of
the
target (D26) directly in the worksheet. However, I want
this event to be triggered by a user entering a date in
another worksheet (the target worksheet will not have
focus). Target is difference between TODAY() and date
entered by user so it will change when the user enters
date.

Douglas was asking about this on March 7 & 8 but I can't
find the solution. Any suggestions?

Thanks so much! You are all so helpful!
Kathryn

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D26")) Is Nothing Then
Exit
Sub
On Error GoTo CleanUp:
With Target
If .Value Then
Application.EnableEvents = False
Dim Msg, Style, Title, Help, Ctxt, Response,
MyString
Msg = "Do you want to continue ?" '
Define
message.
Style = vbYesNo + vbCritical +
vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define
title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help,
Ctxt)
If Response = vbYes Then ' User chose
Yes.
MyString = "Yes" ' Perform some
action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

.