checkbox value change

[pv78]

I am writing a simple procedure to uncheck a checkbox if a certain
condition is met but I think it is executing the whole procedure again
on the value change step.

Try pasting this code into excel after creating a checkbox. Not sure
why the msgbox comes up again after I click on no.

Private Sub CheckBox1_Click()
Dim s As Integer

s = MsgBox("Delete numbers?", vbYesNo)
If s = vbYes And CheckBox1 = True Then
'Do stuff
Else
'erase tick in checkbox since nothing was done
CheckBox1 = False 'This is where it brings up the msgbox again
which it shouldn't

End If

End Sub


Thanks for your help!

 

[Die_Another_Day]

It's because you are changing the Checkbox value, thus trigger the
event again. One easy solution is this:
Sub blahblah
Application.EnableEvents = False
'BlahBlahCode
Application.EnableEvents = True
End Sub

 

[pv78]

Thanks for the quick reply Charles but it didn't work. Still gives me a
double msgbox.

 

[Die_Another_Day]

Well I have a kluge fix for you. Hopefully one of the MVP's will show
us a better way.
Create a public boolean then check if that is set like so:

Dim Running as Boolean

Private Sub CheckBox1_Click()

If Running Then Exit Sub

If MsgBox("Delete numbers?", vbYesNo) = vbYes And CheckBox1.Value =
True Then
'Do stuff
Else
'erase tick in checkbox since nothing was done
Running = True
CheckBox1 = False 'This is where it brings up the msgbox again
which it shouldn't
End If

Running = False
End Sub

Therefore when you hit the "else" statement you set running to true, so
that when it re-triggers the event it bypasses the 2nd event and exits
gracefully. I know it's klugy but it does work.

Charles

 

[pv78]

Awesome! Thanks Charles