View Full Version : Unstable event code
Hi,
I'm having problems with the attached event code. Sometimes it works perfectly, sometimes it doesn't. Any thoughts?
Basically, I want to clear all "A1 related cells" if test_type = N and clear all "A2 related cells" if test_type = Y, with appropriate msg boxes warning the user these cells will be cleared.
Thanks!
frank_m
06-27-2011, 12:11 AM
Hi Edi,
See if this code works any better, as your select case arrangement looked a little unusual to me
Also, is setting enable events to true necessary? is it set to false elsewhere?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
If Target.Address = _
ThisWorkbook.Worksheets("Input").Range("A1_test").Address And _
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "N" Then
Application.ScreenUpdating = False
i = MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Select Case i
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("A1_test_type, Level").ClearContents
End With
GoTo Finish
Case vbCancel
MsgBox "No fields have been reset"
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "Y"
GoTo Finish
End Select
End If
If Target.Address = _
ThisWorkbook.Worksheets("Input").Range("A1_test").Address And _
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "Y" Then
i = MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Select Case i
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("A2_initial_date, A2_start_date, A2_duration").ClearContents
End With
GoTo Finish
Case vbCancel
MsgBox "No fields have been reset"
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "N"
GoTo Finish
End Select
End If
Finish:
Application.ScreenUpdating = True
'Application.EnableEvents = True
End Sub
Bob Phillips
06-27-2011, 12:49 AM
I think your were leaving EnableEvents unset
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = True
If Target.Address = _
ThisWorkbook.Worksheets("Input").Range("A1_test").Address And _
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "N" Then
Select Case _
MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("A1_test_type, Level").ClearContents
End With
Case vbCancel
MsgBox "No fields have been reset"
Application.EnableEvents = False
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "Y"
Application.EnableEvents = True
Exit Sub
End Select
End If
If Target.Address = _
ThisWorkbook.Worksheets("Input").Range("A1_test").Address And _
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "Y" Then
Select Case _
MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("A2_initial_date, A2_start_date, A2_duration").ClearContents
End With
Case vbCancel
MsgBox "No fields have been reset"
Application.EnableEvents = False
ThisWorkbook.Worksheets("Input").Range("A1_test").Value = "N"
Application.EnableEvents = True
Exit Sub
End Select
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Paul_Hossler
06-27-2011, 02:24 PM
XLD --
Just checking my understanding, but would there be any way that the Worksheet_Change would run with Application.EnableEvents not True, making the .EnableEvents unnecessary?
Private Sub Worksheet_Change(ByVal Target As Range)
....
Application.EnableEvents = True
Paul
it worked! That's awesome
Just one thing...if I toggle from "N" to a "Y" no message boxes pop up and the content doesn't clear. Is there any way I can fix this?
Thanks heaps!
Bob Phillips
06-28-2011, 02:43 AM
XLD --
Just checking my understanding, but would there be any way that the Worksheet_Change would run with Application.EnableEvents not True, making the .EnableEvents unnecessary?
Private Sub Worksheet_Change(ByVal Target As Range)
....
Application.EnableEvents = True
Paul
No, events don't fire if EnableEvents = False
Bob Phillips
06-28-2011, 02:45 AM
it worked! That's awesome
Just one thing...if I toggle from "N" to a "Y" no message boxes pop up and the content doesn't clear. Is there any way I can fix this?
Which reply worked?
CatDaddy
06-28-2011, 11:40 AM
Which reply worked?
just a point of curiosity, why did you EnableEvents so many times in your code?
Hi guys,
I've tried playing around with the "Y" / "N" toggle and it doesn't appear to work if I keep switching between "Y" and "N" or from "N" to "Y" several times - any idea how I can fix this?
Thanks
Paul_Hossler
06-29-2011, 04:43 AM
I'd break it up into modules to make it easier to track down problems
I did notice that there's a named range in the VBA that I didn't see in the workbook
Option Explicit
Dim rTest As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo ResetThings
Set rTest = ThisWorkbook.Worksheets("Input").Range("A1_test")
If Target.Address = rTest.Address Then
If rTest.Value = "N" Then
Call NO
ElseIf rTest.Value = "Y" Then
Call YES
Else
Call MsgBox("Not Yes or No", vbOKokonly, "Test switch")
End If
ResetThings:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub YES()
Select Case _
MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Case vbOK
With ThisWorkbook.Worksheets("Input")
'did not see this as defined name range
.Range("A1_test_type, Level").ClearContents
End With
Case vbCancel
MsgBox "No fields have been reset"
rTest.Value = "Y"
End Select
End Sub
Sub NO()
Select Case _
MsgBox("By changing this switch, all associated fields will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Test switch")
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("A2_initial_date, A2_start_date, A2_duration").ClearContents
End With
Case vbCancel
MsgBox "No fields have been reset"
rTest.Value = "N"
End Select
End Sub
Paul
Hi Paul,
Thanks for the suggestion.
I've created separate modules for Sub YES() and Sub NO() and they appear to be working fine if I "manually" run the code.
However, I can't actually call on these modules. I've pasted the following code in Sheet 1 (Input) under "Microsoft Excel objects". Am I doing anything wrong? Thanks!
Option Explicit
Dim rTest As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Goto ResetThings
Set rTest = ThisWorkbook.Worksheets("Input").Range("A1_test")
If Target.Address = rTest.Address Then
If rTest.Value = "N" Then
Call NO
ElseIf rTest.Value = "Y" Then
Call YES
Else
Call MsgBox("Not Yes or No", vbOKokonly, "Test switch")
End If
ResetThings:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Bob Phillips
06-30-2011, 12:18 AM
There are two errors in your code, that apart it works fine for me.
Option Explicit
Dim rTest As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Goto ResetThings
Set rTest = ThisWorkbook.Worksheets("Input").Range("A1_test")
If Target.Address = rTest.Address Then
If rTest.Value = "N" Then
Call NO
ElseIf rTest.Value = "Y" Then
Call YES
Else
Call MsgBox("Not Yes or No", vbOKonly, "Test switch")
End If
End If
ResetThings:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Hi guys,
Works perfectly now. Thanks for your help!
I'm now trying to create another event code which is very similar with a slight tweak.
In the previous example, "A1_test" could be one of 2 values - "Y" or "N". Hence, when the user switches from "Y" to "N" but then hits the cancel button, the code forces "A1_test" to = "N". Likewise if the user switches from "N" to "Y", the code would force "A1_test" to = "Y" if the user hits the cancel button.
However, if test switch is actually a list of 10 variables. How can I change the code such that it will automatically revert back to it's original value if the user selects the cancel button.
I tried doing the following and other variations of this without any luck. Thanks!
Option Explicit
Dim subsetTest As Range
If Target.Address = subsetTest.Address Then
Call subset_switch
End If
...and in a separate module:
Sub subset_switch()
Dim temp As Range
Set temp = ThisWorkbook.Worksheets("Input").Range("subset")
Select Case _
MsgBox("By changing this switch, all subcategories will be reset. Do you wish to proceed?", _
vbOKCancel Or vbExclamation Or vbDefaultButton2, "Subset switch")
Case vbOK
With ThisWorkbook.Worksheets("Input")
.Range("scenarios_subset").ClearContents
End With
Case vbCancel
MsgBox "No fields have been reset"
ThisWorkbook.Worksheets("Input").Range("subset") = temp.Value
End Select
End Sub
Paul_Hossler
06-30-2011, 05:07 AM
There are two errors in your code, that apart it works fine for me.
I found the missing EndIF :( , but what was the other one :dunno ?
Paul
Hi Paul,
The other change is from "vbOKokonly" to "vbOKonly".
Can anyone please help me as to how I can revert back to the original value once I toggle the target cell?
Thanks!
Bob Phillips
07-01-2011, 01:34 AM
Something like this
Option Explicit
Private prevValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
On Error GoTo ResetThings
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1_test")) Is Nothing Then
If MsgBox("Value OK?", vbOKCancel, "Confirm Value") = vbCancel Then
Target.Value = prevValue
Else
prevValue = Target.Value
End If
End If
ResetThings:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1_test")) Is Nothing Then
prevValue = Target.Value
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.