Unstable event code

[Edi]

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]

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]

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]

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

[Edi]

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]

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]

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]

Which reply worked?

just a point of curiosity, why did you EnableEvents so many times in your code?

[Edi]

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]

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

[Edi]

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]

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

[Edi]

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]

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

[Edi]

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]

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