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?
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?
They are different events, so you distinguish by trapping the separate events, but maybe I am missing what you actually mean.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.Originally Posted by xld
Set up a global boolean variable such as DoubleClickCodeIsRunning.
In the doubleclick event code, have the line:
before it that code makes any changes to the worksheet.DoubleClickCodeIsRunning=True
Add another line to the same code:
after all changes made to the worksheet by that code are complete.DoubleClickCodeIsRunning=False
In the WorksheetChange code, encapsulate all the code with:
If not DoubleClickCodeIsRunning then 'your existing code End if
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
That appears to be doing the trick. Thanks.
Can you help me with my code?
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False Set ws1 = Worksheets("IRSDKM") ws1.Unprotect Password:="khthonik" If Intersect(Target, ws1.[B:S, V:AC]) Is Nothing Then Exit Sub ws1.Cells(Target.Row, "AE") = Application.UserName & " " & Format(Now, "dd.mm.yyyy hh:mm:ss") With ws1 .Protect Password:="khthonik", userinterfaceonly:=True, AllowFiltering:=True .EnableOutlining = True End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("AN:AN")) Is Nothing Then Cancel = True Target.Formula = Date End If End Sub
Last edited by Paul_Hossler; 12-27-2023 at 08:06 AM.
1. Welcome to the forums. Please takea minute to read the FAQ at the link in my signature
2. It's better to start your own new post instead of attaching a question to an 11 year old one
3. We like to have code set off with CODE tags singe it makes it easier to read and does some formatting
4. Finally, the answer to ...
... is 'Yes' but you'll need to give us a hint as to what help you want, i.e. correct it, improve it, etc.Can you help me with my code?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hello,
I want to use these two macros "Private Sub Worksheet_Change(ByVal Target As Range)" and "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" in a worksheet. But the codes don't work. That's why I asked for help using "DoubleClick Code IsRunning=False" and "DoubleClick CodeIsRunning=True".
Thanks.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("AN:AN")) Is Nothing Then Cancel = False Target.Formula = Date End If End Sub
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Can you edit these two codes in a worksheet so that they work properly?
What do you mean by "properly"? What is the problem?
Be as you wish to seem
This codes do not work in one worksheet (It doesn't give any errors and it works once and then stops)
Last edited by Aussiebear; 01-04-2024 at 02:57 AM. Reason: Second question added
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
The Change event is badly structured. The Exit Sub can occur after events are disabled and therefore leave them that way. Amend it to something like:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws1 As Worksheet Set ws1 = Worksheets("IRSDKM") If Not Intersect(Target, ws1.[B:S, V:AC]) Is Nothing Then With Application .ScreenUpdating = False .EnableEvents = False End With ws1.Unprotect Password:="khthonik" ws1.Cells(Target.Row, "AE") = Application.UserName & " " & Format(Now, "dd.mm.yyyy hh:mm:ss") With ws1 .Protect Password:="khthonik", userinterfaceonly:=True, AllowFiltering:=True .EnableOutlining = True End With End If clean_up: Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub err_handle: Resume clean_up End Sub
Last edited by Aflatoon; 01-04-2024 at 05:03 AM. Reason: Corrected code
Be as you wish to seem
Yes, only once because of the double click. I added to my answer above.
Not in the module. In the "IRSDKM" page.
Sorry kothonik but I'm not opening a file from a server I have no experience with. I note your file is 3mb. Any chance a sample file is much smaller? BTW, Click on Go Advanced/ Manage Attachments/ Choose a file/Upload to attach a sample file here at this website.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Greetings,
I agree with Aussiebear, loading a simplified workbook here is better. I did take a look however, and find myself guessing a bit. Does this help?
MarkOption Explicit Dim bolInProcess As Boolean Private Sub Worksheet_Change(ByVal Target As Range) Dim ws1 As Worksheet If Not bolInProcess Then 'Use here to prevent recurse bolInProcess = True Application.ScreenUpdating = False Set ws1 = Worksheets("IRSDKM") With ws1 .Protect Password:="EYIL", UserInterfaceOnly:=True, AllowFiltering:=True .EnableOutlining = True End With If Intersect(Target, ws1.[B:S, V:AC]) Is Nothing Then Exit Sub ws1.Cells(Target.Row, "AE") = Application.UserName & " " & Format(Now, "dd.mm.yyyy hh:mm:ss") Application.ScreenUpdating = True bolInProcess = False End If End Sub Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim ws1 As Worksheet If Target.Column = 40 Then ' Use a module level flag to skip code in Worksheet_Change. bolInProcess = True Cancel = True Set ws1 = Worksheets("IRSDKM") With ws1 .Protect Password:="EYIL", UserInterfaceOnly:=True, AllowFiltering:=True .EnableOutlining = True End With Target.Formula = Format(Now, "dd.mm.yyyy hh:mm:ss") Target.Offset(, 1).Value = Target.Offset(, 1).Value bolInProcess = False End If End Sub
Thank you very much. Codes are working.