checkbox value change
 

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

checkbox value change
 

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

checkbox value change
 

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

checkbox value change
 

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

checkbox value change
 

Awesome! Thanks Charles

--
pv7
-----------------------------------------------------------------------
pv78's Profile: http://www.excelforum.com/member.php...fo&userid=3755
View this thread: http://www.excelforum.com/showthread.php?threadid=57188