Checkbox from toolbox

Hi All

I have a worksheet that contains a number of checkboxes (all from the
Control toolbox rather than the forms toolbar.

I wish to have a button on the sheet which, when clicked, 'resets' all
of the checkboxes to unchecked.

Is there a quick and easy way of doing this?

Many thanks for your help on this.

Andy

Hello Andy,

This will clear all the checkboxes it finds on the active worksheet, regardless of their names. Copy this code and placce it in a VBA module.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thank you Leith. Works perfectly.

Just to clarify and hopefully learn something in the process.

So the word OLEobjects refers to all control toolbox buttons, checkboxes
etc on the sheet.

So the code counts all the OLEObjects on the sheet. Using a for loop It
then checks each one to ensure it is a checkbox and then assigns the
value FALSE (unchecked).

Thanks again

Andy

Leith Ross wrote:
> Hello Andy,
>
> This will clear all the checkboxes it finds on the active worksheet,
> regardless of their names. Copy this code and placce it in a VBA
> module.
>
>
> Code:
> --------------------
> Sub ClearAllCheckBoxes()
>
> Dim ChkBoxId As String
>
> ChkBoxId = "Forms.CheckBox.1"
>
> With ActiveSheet
> For I = 1 To .OLEObjects.Count
> If .OLEObjects(I).ProgId = ChkBoxId Then
> .OLEObjects(I).Object.Value = False
> End If
> Next I
> End With
>
> End Sub
>
> --------------------
>
> Sincerely,
> Leith Ross
>
>

Hello Andy,

You are correct. The Control Toolbox controls are embedded objects on the Worksheet, that's why they are referenced using OLEObject. For those who don't know, OLE stands for Object Linking and Embedding.

The Object property allows you to access the properties of the embedded object, in this case the value property of the checkbox. The value property when true displays the checkmark, and removes it when false.

Sincerely,
Leith Ross

And there are other ways to check.

Instead of:
If .OLEObjects(I).ProgId = ChkBoxId Then
I'd use:
If typeof .OLEObjects(I).object is msforms.checkbox then

I find it a bit more intuitive. (but it's pretty much dealer's choice.)



Andy wrote:
>
> Thank you Leith. Works perfectly.
>
> Just to clarify and hopefully learn something in the process.
>
> So the word OLEobjects refers to all control toolbox buttons, checkboxes
> etc on the sheet.
>
> So the code counts all the OLEObjects on the sheet. Using a for loop It
> then checks each one to ensure it is a checkbox and then assigns the
> value FALSE (unchecked).
>
> Thanks again
>
> Andy
>
> Leith Ross wrote:
> > Hello Andy,
> >
> > This will clear all the checkboxes it finds on the active worksheet,
> > regardless of their names. Copy this code and placce it in a VBA
> > module.
> >
> >
> > Code:
> > --------------------
> > Sub ClearAllCheckBoxes()
> >
> > Dim ChkBoxId As String
> >
> > ChkBoxId = "Forms.CheckBox.1"
> >
> > With ActiveSheet
> > For I = 1 To .OLEObjects.Count
> > If .OLEObjects(I).ProgId = ChkBoxId Then
> > .OLEObjects(I).Object.Value = False
> > End If
> > Next I
> > End With
> >
> > End Sub
> >
> > --------------------
> >
> > Sincerely,
> > Leith Ross
> >
> >

--

Dave Peterson

Thanks Leith and Dave

This sort of feedback, from both of you, I find very useful as it gives
me a chance to experiment and learn new ways of doing things, which
invariably leads to neater and less code.

Cheers

Andy

Dave Peterson wrote:
> And there are other ways to check.
>
> Instead of:
> If .OLEObjects(I).ProgId = ChkBoxId Then
> I'd use:
> If typeof .OLEObjects(I).object is msforms.checkbox then
>
> I find it a bit more intuitive. (but it's pretty much dealer's choice.)
>
>
>
> Andy wrote:
>
>>Thank you Leith. Works perfectly.
>>
>>Just to clarify and hopefully learn something in the process.
>>
>>So the word OLEobjects refers to all control toolbox buttons, checkboxes
>>etc on the sheet.
>>
>>So the code counts all the OLEObjects on the sheet. Using a for loop It
>>then checks each one to ensure it is a checkbox and then assigns the
>>value FALSE (unchecked).
>>
>>Thanks again
>>
>>Andy
>>
>>Leith Ross wrote:
>>
>>>Hello Andy,
>>>
>>>This will clear all the checkboxes it finds on the active worksheet,
>>>regardless of their names. Copy this code and placce it in a VBA
>>>module.
>>>
>>>
>>>Code:
>>>--------------------
>>> Sub ClearAllCheckBoxes()
>>>
>>> Dim ChkBoxId As String
>>>
>>> ChkBoxId = "Forms.CheckBox.1"
>>>
>>> With ActiveSheet
>>> For I = 1 To .OLEObjects.Count
>>> If .OLEObjects(I).ProgId = ChkBoxId Then
>>> .OLEObjects(I).Object.Value = False
>>> End If
>>> Next I
>>> End With
>>>
>>> End Sub
>>>
>>>--------------------
>>>
>>>Sincerely,
>>>Leith Ross
>>>
>>>
>
>