Prev: Conditional Formatting with Hyperlinks
Next: Can we set the UI color scheme via VB[A} or XML?
From: mooresk257 on 27 May 2010 15:53
Hi,

So, on to my next dilemma - how do I change the value of a check box without
initiating a Checkbox_Change sub attached to it?

I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to
false triggers the change event sub whether the check box is enabled or not.

It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change()
event.

Any suggestions?

Thanks!

Scott
From: GS on 27 May 2010 15:59
mooresk257 wrote on 5/27/2010 :
> Hi,
>
> So, on to my next dilemma - how do I change the value of a check box without
> initiating a Checkbox_Change sub attached to it?
>
> I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to
> false triggers the change event sub whether the check box is enabled or not.
>
> It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change()
> event.
>
> Any suggestions?
>
> Thanks!
>
> Scott

Try...

Application.EnableEvents = False
Checkbox1.Value = False
Application.EnableEvents = True

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: mooresk257 on 27 May 2010 16:24
I put a check box and a command button on a new sheet, with the following
code for each:

Private Sub CheckBox1_Click()

If Sheet1.CheckBox1.Value = True Then
MsgBox ("Yup")
Else: MsgBox ("Nope")
End If

End Sub

Private Sub CommandButton1_Click()

Application.EnableEvents = False

Sheet1.CheckBox1.Value = False

Application.EnableEvents = True

End Sub

However, when I run the command button code to set the check box value to
false, I still get a message box saying "nope". If events are turned off, I
don't see why the code for the check box event would run.

Or am I going about this the wrong way?

"ker_01" wrote:

> There may be easier ways (consider testing "application.events = false"
> before you change the checkbox, and "application.events = true" immediately
> after, that might work?)
>
> but for straight (if inelegant) logic, add another variable
>
> Sub Checkbox_Change
> If MyVariable = True then
> 'all the code here
> end if
> End sub
>
> Sub MyOtherProcedure
> MyVariable = False
> CheckboxA = True
> CheckboxA = False
> MyVariable = True
> end sub
>
> Of course, you will need to set MyVariable to True in the workbook_open
> event, to make sure it is always enabled unless you decide to disable it. I
> think an uninitialized variable will have a null value, so maybe you could
> use that to your advantage, but again, I haven't tested that in actual use.
>
> HTH,
> Keith
>
> "mooresk257" wrote:
>
> > Hi,
> >
> > So, on to my next dilemma - how do I change the value of a check box without
> > initiating a Checkbox_Change sub attached to it?
> >
> > I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to
> > false triggers the change event sub whether the check box is enabled or not.
> >
> > It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change()
> > event.
> >
> > Any suggestions?
> >
> > Thanks!
> >
> > Scott
From: ker_01 on 27 May 2010 17:29
There may be easier ways (consider testing "application.events = false"
before you change the checkbox, and "application.events = true" immediately
after, that might work?)

but for straight (if inelegant) logic, add another variable

Sub Checkbox_Change
If MyVariable = True then
'all the code here
end if
End sub

Sub MyOtherProcedure
MyVariable = False
CheckboxA = True
CheckboxA = False
MyVariable = True
end sub

Of course, you will need to set MyVariable to True in the workbook_open
event, to make sure it is always enabled unless you decide to disable it. I
think an uninitialized variable will have a null value, so maybe you could
use that to your advantage, but again, I haven't tested that in actual use.

HTH,
Keith

"mooresk257" wrote:

> Hi,
>
> So, on to my next dilemma - how do I change the value of a check box without
> initiating a Checkbox_Change sub attached to it?
>
> I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to
> false triggers the change event sub whether the check box is enabled or not.
>
> It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change()
> event.
>
> Any suggestions?
>
> Thanks!
>
> Scott
From: GS on 27 May 2010 17:33
mooresk257 used his keyboard to write :
> I put a check box and a command button on a new sheet, with the following
> code for each:
>
> Private Sub CheckBox1_Click()
>
> If Sheet1.CheckBox1.Value = True Then
> MsgBox ("Yup")
> Else: MsgBox ("Nope")
> End If
>
> End Sub
>
> Private Sub CommandButton1_Click()
>
> Application.EnableEvents = False
>
> Sheet1.CheckBox1.Value = False
>
> Application.EnableEvents = True
>
> End Sub
>
> However, when I run the command button code to set the check box value to
> false, I still get a message box saying "nope". If events are turned off, I
> don't see why the code for the check box event would run.
>
> Or am I going about this the wrong way?
>
> "ker_01" wrote:
>
>> There may be easier ways (consider testing "application.events = false"
>> before you change the checkbox, and "application.events = true" immediately
>> after, that might work?)
>>
>> but for straight (if inelegant) logic, add another variable
>>
>> Sub Checkbox_Change
>> If MyVariable = True then
>> 'all the code here
>> end if
>> End sub
>>
>> Sub MyOtherProcedure
>> MyVariable = False
>> CheckboxA = True
>> CheckboxA = False
>> MyVariable = True
>> end sub
>>
>> Of course, you will need to set MyVariable to True in the workbook_open
>> event, to make sure it is always enabled unless you decide to disable it. I
>> think an uninitialized variable will have a null value, so maybe you could
>> use that to your advantage, but again, I haven't tested that in actual use.
>>
>> HTH,
>> Keith
>>
>> "mooresk257" wrote:
>>
>>> Hi,
>>>
>>> So, on to my next dilemma - how do I change the value of a check box
>>> without initiating a Checkbox_Change sub attached to it?
>>>
>>> I have a check box (Sheet1.CheckBox1.Value = True) and changing the value
>>> to false triggers the change event sub whether the check box is enabled or
>>> not.
>>>
>>> It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change()
>>> event.
>>>
>>> Any suggestions?
>>>
>>> Thanks!
>>>
>>> Scott

That's because EnableEvents apparently doesn't work in this case. I've
never tried this before and so was recommending you 'try' my
suggestion.

I have tested this as of reading your new post. Here's what I
recommend:

1. Add the following line to the declarations section of the sheet
code module:
Dim bDisableEvents As Boolean

In your command button code:
bDisableEvents = True
Checkbox1.Value = False
bDisableEvents = False

In your Checkbox1_Click OR _Change event:
If bDisableEvents Then Exit Sub

Now, whenever you use the command button you won't get the msgbox.
Whever you click the checkbox you will only get the msgbox if it's
checked.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 |  Next  |  Last
Pages: 1 2
Prev: Conditional Formatting with Hyperlinks
Next: Can we set the UI color scheme via VB[A} or XML?