Need an elegant method of clearing form checkboxes

deepfriedcheese

New Member
Joined
Aug 6, 2010
Messages
21
I have a form with ~100 checkboxes from the Forms toolbox. This is in it's default state. Users can click a button to add more as necessary. Users will need to reset the form to it's default state periodically as well, and that's the rub. All of those checkboses need to be reset to blank.

I can't link each textbox to a cell because that complicates the whole thing dramatically.

I tried setting the value of the checkboxes directly in the macro, but I can only get it to work for ActiveX checkboxes, which these are not. If someone knows a way to do that, I'd dearly love to hear it.

My only other idea is to loop through all the checkboxes and link them all to the same cell. Set that cell to zero, then loop back through and remove the cell link. It's inelegant, but I think it would work.

Does anyone have a better plan?
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Have you tried:

ActiveSheet.CheckBoxes.Value = False

HTH,
 

deepfriedcheese

New Member
Joined
Aug 6, 2010
Messages
21
@Smitty - That is about as simple and elegant as it gets! I tried several variations, but it looks like I was overcomplicating it.

@shg - The Marlett boxes sound interesting, but for this use I think the form checkbox is adequate. I don't want to replace 100 or more boxes right now.

Thank you both for your help.
 

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
You could add a huge number of checkboxes that way. Name each of the checkboxes real fast by using "Create from Selection". Put the names of the checkboxes to the left like Box1, Box2... fill down. Start the naming process and choose Left as the option. It names all the boxes at once. Change the font to marlett and then add some code to auto change them. Name the entire range as CheckboxRange. You can control them individually or all at once.

I really like this method!

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Set i = Intersect(Target, Range("CheckBoxRange"))
  If Not i Is Nothing Then
    If Target.Text = "a" Then
      Target = ""
    Else
      Target = "a"
    End If
    Cancel = True
  End If
  
End Sub
 

Marcol

Well-known Member
Joined
Mar 1, 2010
Messages
644
With c100 checkboxes and ...
This is in it's default state. Users can click a button to add more as necessary.
You certianly know how to make a stick for your own back!

Go with shg, simplicity is the finest form of elegance!
 

Forum statistics

Threads
1,085,288
Messages
5,382,736
Members
401,801
Latest member
tins

Some videos you may like

This Week's Hot Topics

Top