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

K

Kathryn

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
 
F

Frank Kabel

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

Kathryn

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



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top