PDA

View Full Version : Solved: Worksheet_Change vs Worksheet_BeforeDoubleClick



Opv
05-05-2012, 02:38 PM
Is there a way to distinguish a change made to a cell via manual entry and then clicking Return/Enter from a change made via VBA in a Worksheet_BeforeDoubleClick Sub?

xld
05-05-2012, 04:31 PM
They are different events, so you distinguish by trapping the separate events, but maybe I am missing what you actually mean.

Opv
05-05-2012, 04:44 PM
They are different events, so you distinguish by trapping the separate events, but maybe I am missing what you actually mean.
Yes, that's my problem. I have a Worksheet_BeforeDoubleClick event that inserts a series of formulas and values into the designated Target cells, and it works fine. I was attempting to also have a Worksheet_Change event so that if I later needed to make a manual change to one of the values, I could call the appropriate subroutine. My problem is that when the Intersect condition is set within the Worksheet_Change block of code, I have no way to distinguish whether the change that occurs to Target is the result of the Before Double Click code or the Worksheet Change code. The Worksheet Change code actually runs in both instances. Needless to say, this is causing some rather bizarre behavior. I was just wondering whether I could identify the source of the change so that I could set up a condition within the Worksheet Change code so that if the change was the result of a Double Click action then I could bypass that particular block of code.

p45cal
05-06-2012, 04:32 AM
Set up a global boolean variable such as DoubleClickCodeIsRunning.
In the doubleclick event code, have the line:
DoubleClickCodeIsRunning=True
before it that code makes any changes to the worksheet.
Add another line to the same code:
DoubleClickCodeIsRunning=False
after all changes made to the worksheet by that code are complete.

In the WorksheetChange code, encapsulate all the code with:
If not DoubleClickCodeIsRunning then
'your existing code
End if

Opv
05-06-2012, 06:39 AM
That appears to be doing the trick. Thanks.