Solved: unchecking checkboxes

[mferrisi]

I have a worksheet that contains many checkboxes. At the start of the code, I want to uncheck all of the checkboxes. As they were not created from the toolbar, setting value=False does not work. Is there a code like shapes("checkbox1").UNCHECKTHEBOX = TRUE that makes this possible?

Any help is greatly appreciated.

[Bob Phillips]

If its a control toolbox checkbox then try

[vba]

ActiveSheet.OLEObjects("CheckBox1").Object = True
[/vba]

[mferrisi]

Thank you!

[mferrisi]

That works great, but it leads to one other small problem:

Some of the checkboxes have 'onaction' commands, so when the boxes are unchecked, the onaction procedure runs. Is there a way to turn this off and on so that the boxes can all be unchecked? Thank you!

[Bob Phillips]

OnAction? Do you mean there is a Click event procedure?

[mferrisi]

Right-- so when the code unchecks the box, the OnEvent procedure runs. How can I turn off this OnEvent procedure? Thank you very much,

[mferrisi]

I mean ONAction.

[Bob Phillips]

I'm confused, isn't OnAction a commandbar property?

[mferrisi]

Right. The boxes, when checked or unchecked by the user activate an OnAction procedure to run. In design mode, I can right click on a box, select the "view code" option, and go to the code that runs when the box is checked or unchecked.

I added code that unchecks all of the boxes.
[VBA] For Each obj In ws.OLEObjects
obj.Object.Value = False
Next obj[/VBA]

Howver, boxes run this procedure:
[VBA]Private Sub CheckBox1_Click()
Call Display_II
End Sub[/VBA]

so that now, when the first box is to be unchecked, the code runs "Display_II" and none of the other boxes get unchecked. So, I guess I need a way for all of the boxes to be set to false, without the code getting interupted to run the CheckBox_click routines.

Thank you

[Bob Phillips]

But I am saying that OnACtion is a commandbar property, checkboxes are controls, so there is a mis-match somewhere.

[mferrisi]

My apologies for being confusing (I'm a bit confused myself). Thank you for your patience.

Let me make sure I have this correct: There are two types of buttons/bars/etc. One of the types I can right click on at anytime, and I have the option to "Assign Macro". The other type I have to be in design mode to select options, the "Assign Macro" is not one of them; instead, I have to go to "View Code" and put the code in the sub. The check boxes are of the second variety.

Everything worked fine until I decided I wanted to uncheck all of the boxes. I hope that makes it a bit more clear.

[Bob Phillips]

You need to create a public variable that signifies you want to suppres the evnt procedure, like this

[vba]

Global ActiveXOff As Boolean

Sub TurnOnCheckbox()
ActiveXOff = True
ActiveSheet.OLEObjects("CheckBox1").Object = True
ActiveXOff = False
End Sub
[/vBa]

and then in the evnt code for the checkbox, test that variable, like this

[vba]

Private Sub CheckBox1_Click()
If Not ActiveXOff Then
MsgBox "hello"
End If
End Sub
[/vba]

[mferrisi]

Thank you very much.