PDA

View Full Version : Solved: unchecking checkboxes



mferrisi
05-09-2008, 01:40 PM
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
05-09-2008, 01:55 PM
If its a control toolbox checkbox then try



ActiveSheet.OLEObjects("CheckBox1").Object = True

mferrisi
05-09-2008, 02:06 PM
Thank you!

mferrisi
05-09-2008, 02:50 PM
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
05-09-2008, 03:17 PM
OnAction? Do you mean there is a Click event procedure?

mferrisi
05-12-2008, 06:40 AM
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
05-12-2008, 06:50 AM
I mean ONAction.

Bob Phillips
05-12-2008, 06:56 AM
I'm confused, isn't OnAction a commandbar property?

mferrisi
05-12-2008, 07:40 AM
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.
For Each obj In ws.OLEObjects
obj.Object.Value = False
Next obj

Howver, boxes run this procedure:
Private Sub CheckBox1_Click()
Call Display_II
End Sub

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
05-12-2008, 07:50 AM
But I am saying that OnACtion is a commandbar property, checkboxes are controls, so there is a mis-match somewhere.

mferrisi
05-12-2008, 08:16 AM
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
05-12-2008, 08:30 AM
You need to create a public variable that signifies you want to suppres the evnt procedure, like this



Global ActiveXOff As Boolean

Sub TurnOnCheckbox()
ActiveXOff = True
ActiveSheet.OLEObjects("CheckBox1").Object = True
ActiveXOff = False
End Sub


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



Private Sub CheckBox1_Click()
If Not ActiveXOff Then
MsgBox "hello"
End If
End Sub

mferrisi
05-12-2008, 09:30 AM
Thank you very much.