Clear certain checkboxes

[Arlen]

Is there a way to identify and clear only a certain subset of checkboxes on a
worksheet?

I've got the ActiveSheet.checkboxes.value = xlOff, but of course, that
clears all checkboxes.

So...just wanting to know.

Thank you for your time.

 

[Dave Peterson]

If you know the names of the checkboxes, you could loop through all of them
looking for a match by name.

If you know the location/range of the checkboxes to be cleared, you could loop
through all of them and compare the .topleftcell of that checkbox with the
range.

How do you know which ones to clear?

 

[Arlen]

Dave,

Can I name just these 5 specific checkboxes through the Format Control menu?
If I can, then what does the macro look like which identifies the names of
those 5...

 

[Dave Peterson]

Select the textbox you want to name. (I just rightclick on it.)
Type the new name in the namebox -- to the left of the formulabar.

Type the new name and hit enter.

Then you could use:

activesheet.checkboxes("myCheckbox99").value = xloff
activesheet.checkboxes("CBX_SendEmail").value = xloff
activesheet.checkboxes("CBX_Whateveryouwant").value = xloff

(Remember that these are the checkboxes from the Forms toolbar -- not checkboxes
from the control toolbox toolbar -- but that matches your original code.)

 

[Chip Pearson]

If you are using checkboxes from the Forms control bar, use

Dim N As Long
Dim V As Variant
V = Array("CheckBox1", "CheckBox3")
For N = LBound(V) To UBound(V)
ActiveSheet.CheckBoxes(V(N)).Value = False
Next N

List in the Array those checkboxes you want to clear.

If you are using checkboxes from the Controls (ActiveX) control bar,
use

Dim N As Long
Dim V As Variant
Dim WS As Worksheet
V = Array("cbx1", "cbx3")
For N = LBound(V) To UBound(V)
ActiveSheet.OLEObjects(V(N)).Object.Value = False
Next N

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com