Cancel change event?

[NuJoizey]

A user mistakenly clicks a checkbox from False to True. I want a message box to pop up saying "Are you sure?" If user responds "NO" then I want to exit out of the routine and set the checkbox back to False. But the problem is that when I do this, the event is re-fired and AddCols() procedure keeps getting called - but I don't want this to happen. So then, how are you supposed to capture a mistake by user and roll back the action? This is driving me crazy!

Private Sub chk_Change()

If Me.chk.Value = True And Range("D14") > 1 Then
'delete everything from column e to the last column where there is data entry
If MsgBox("Are you sure?") = vbYes Then
Call DeleteCols()
Else
'reset the checkbox
Me.chk.Value = False
Exit Sub
End If
Else
'I DON'T WANT THIS TO BE CALLED IF THE USER ANSWERS "NO" TO "ARE YOU SURE"
Call AddCols()
End If

End Sub

 

[Hermanito]

Application.EnableEvents = False
Me.chk.Value = False 
Application.EnableEvents = True

Add the 2 lines of code as shown above. The statement that changes your checkbox value is the one that causes the re-firing, so you want to disable events when doing that...

 

[NuJoizey]

Thanks Hermanito, but that didn't seem to work. The change event still fires even though Application.EnableEvents = False is called first. What now???

 

[al_b_cnu]

Hi,

Perhaps something like:

Private Sub chk_Change()
Static bIgnore As Boolean

If bIgnore Then
    bIgnore = False
    Exit Sub
End If
If Me.chk.Value = True And Range("D14") > 1 Then
'delete everything from column e to the last column where there is data entry
If MsgBox(prompt:="Are you sure?", Buttons:=vbYesNo) = vbYes Then
Call DeleteCols
Else
'reset the checkbox
    bIgnore = True
    Me.chk.Value = False
    Exit Sub
End If
Else
'I DON'T WANT THIS TO BE CALLED IF THE USER ANSWERS "NO" TO "ARE YOU SURE"
Call AddCols
End If

End Sub

note the amended MsgBox

 

[NuJoizey]

Alan: thank you ever so much. you have positively made my day.

I guess the crux of this issue had to do with the declaration of the Static variable, which admittedly I don't know much about, so I will read up on it and educate myself.

I did note your ammended MsgBox - it seems to work both your way, and the way I had it. Is there a particular reason why you recommend one syntax of the other.

Again, I am much obliged.

 

[al_b_cnu]

Hi NuJoisey,

Yes, a static variable retains it's value even after it has gone out of scope & back in again.

The difference between the 2 MsgBoxes is that yours didnt have the option for the user to select 'Yes' or 'No' - (s)he was only able to select 'OK'

 

[NuJoizey]

right, ok I see what you mean about the message box. I accidentally chopped off that portion of the syntax when i copied the code over into the forum.

It was originally:

If MsgBox("Are you sure you wish to continue?", vbYesNo, "Warning - Possible data deletion...") = vbYes Then

..and so i think I will find the static declaration very useful, i really don't know how i got along this far without using it.