Need an elegant method of clearing form checkboxes

[deepfriedcheese]

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]

Have you tried:

ActiveSheet.CheckBoxes.Value = False

HTH,

 

[shg]

I'd suggest you dump all of the Forms checkboxes in favor of Marlett checkboxes. See, for example, [SOLVED] Event Driven checkbox. Chosing one is erasing the others

Then make all those cells part of a named range, and all you need to do is clear the range.

 

[deepfriedcheese]

@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]

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!

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]

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!