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,081,779
Messages
5,361,238
Members
400,618
Latest member
Gamaway

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top