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

Charles
pv78 wrote:
> 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!
>
>
> --
> pv78
> ------------------------------------------------------------------------
> pv78's Profile: http://www.excelforum.com/member.php...o&userid=37558
> View this thread: http://www.excelforum.com/showthread...hreadid=571889

[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 wrote:
> Thanks for the quick reply Charles but it didn't work. Still gives me a
> double msgbox.
>
>
> --
> pv78
> ------------------------------------------------------------------------
> pv78's Profile: http://www.excelforum.com/member.php...o&userid=37558
> View this thread: http://www.excelforum.com/showthread...hreadid=571889

[pv78]

Awesome! Thanks Charles!