Solved: Worksheet_Change vs Worksheet_BeforeDoubleClick

[Opv]

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?

[Bob Phillips]

They are different events, so you distinguish by trapping the separate events, but maybe I am missing what you actually mean.

[Opv]

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]

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]

That appears to be doing the trick. Thanks.

[khthonik]

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

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

[Paul_Hossler]

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 ...

Can you help me with my code?

... is 'Yes' but you'll need to give us a hint as to what help you want, i.e. correct it, improve it, etc.

[khthonik]

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.

[Aussiebear]

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

[khthonik]

Can you edit these two codes in a worksheet so that they work properly?

[khthonik]

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

Can you edit these two codes in a worksheet so that they work properly?

[Aflatoon]

What do you mean by "properly"? What is the problem?

[khthonik]

This codes do not work in one worksheet (It doesn't give any errors and it works once and then stops)

[Aussiebear]

This codes do not work in one worksheet (It doesn't give any errors and it works once and then stops)

Finally a hint.... So the codes do work but only once. Can you attach a sample workbook where the codes only work once?

As a further thought. Where is the Codes located? as in In a separate module or attached to a worksheet?

[Aflatoon]

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

[khthonik]

231226 Lojistik Hakediş_EY.xlsm - 3,0 MB

[khthonik]

Yes, only once because of the double click. I added to my answer above.


Not in the module. In the "IRSDKM" page.

[Aussiebear]

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.

[GTO]

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?

Option 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

Mark

[khthonik]

Thank you very much. Codes are working.