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?

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

khthonik
12-27-2023, 07:17 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

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
12-27-2023, 08:11 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 ...


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
12-28-2023, 11:15 PM
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
12-29-2023, 05:52 PM
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
01-02-2024, 01:46 AM
Can you edit these two codes in a worksheet so that they work properly?

khthonik
01-02-2024, 01:48 AM
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
01-03-2024, 03:48 AM
What do you mean by "properly"? What is the problem?

khthonik
01-03-2024, 10:52 PM
This codes do not work in one worksheet (It doesn't give any errors and it works once and then stops)

Aussiebear
01-04-2024, 02:52 AM
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
01-04-2024, 03:02 AM
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
01-04-2024, 06:36 AM
231226 Lojistik Hakediş_EY.xlsm - 3,0 MB (https://dosya.co/l2o09xhjngiv/231226_Lojistik_Hakediş_EY.xlsm.html)

khthonik
01-08-2024, 01:13 AM
Yes, only once because of the double click. I added to my answer above.


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

Aussiebear
01-08-2024, 03:44 AM
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
01-08-2024, 11:50 PM
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
01-09-2024, 12:22 AM
Thank you very much. Codes are working.

GTO
01-09-2024, 12:35 AM
Thank you for the feedback and you are most welcome.