How to clear all checkboxes, option buttons & list boxes on a form?

[scott14]

Is there an easy way for a VBA NOVICE (barely understand what I'm doing) to clear all the boxes on a form?

I have 2 simple forms. Each has several Checkboxes, Option Buttons and List Boxes.

How can I put a button on each form to clear all the boxes?

On another forum I found this sub that does work, but only for CheckBoxes...is there a way to make it also work for Option Buttons and List Boxes all in one go?

Sub Clear_Checkbox()
Dim box As OLEObject
For Each box In ActiveSheet.OLEObjects
If TypeName(box.Object) = "OptionButton" Then
box.Object.Value = False
End If
Next box
End Sub

Advice appreciated...please make it simple. THANK YOU!

 

[noopura112]

Do you want to put your workbook on a share like Box.net so that we can take a look at it?

I will design this excel sheet again. because there is some data which i can't disclose. but if it still does not work after putting all these controls on new excel sheet then i will share this workbook.

 

[noopura112]

I am glad to say both of you that when i placed controls on new worksheet both codes (#20 and #21) are working perfectly. I thank both of you from the bottom of my heart for your efforts and i aplogize for the misunderstandings i created.

 

[Gary_S]

Hello Andrew,

This piece of code has proved to be very useful to me and I wanted to thank-you.

However, I did have problems getting it to work (and I am a VBA starter). The problem was I had 'grouped' my checkboxes (trying to get everything lined up neatly). Can you spare a moment to explain why the code would not work and how it might have been changed to allow it to work on grouped controls?

(I am of the 'teach a man to fish' mentality. Don't just give me a fish - show me how to catch one too!)

Kindest Regards,

Gary

For controls on a worksheet try:

Sub ClearAll()
    Dim box As OLEObject
    For Each box In ActiveSheet.OLEObjects
        If TypeName(box.Object) = "OptionButton" Then
            box.Object.Value = False
        ElseIf TypeName(box.Object) = "CheckBox" Then
            box.Object.Value = False
        ElseIf TypeName(box.Object) = "ListBox" Then
            box.Object.Clear
        End If
    Next box
End Sub

 

[BVOPP]

it is odd but in my case it does not work....

i have 3 boxes in my sheet to start a macro and after i copy the sheet to a new sheet i want the boxes to be deleted in the copied sheet.

i added 2 lines to make sure the correct sheet is selected and i also tried to add an check box but nothing works

in debugging i see it skips from the for statement to the end sub statement ... so it does not recognize any boxes at all...


Sub ClearAll_Controls(This_Sheet)
'
' Dim box As OLEObject

Sheets(This_Sheet).Select
Worksheets(This_Sheet).Activate

For Each box In ActiveSheet.OLEObjects
If TypeName(box.Object) = "OptionButton" Then
box.Object.Value = False
ElseIf TypeName(box.Object) = "CheckBox" Then
box.Object.Value = False
ElseIf TypeName(box.Object) = "Button" Then
box.Object.Value = False
ElseIf TypeName(box.Object) = "ListBox" Then
box.Object.Clear
End If
Next box
End Sub

 

[RoryA]

@BVOPP

Did you add Form checkboxes, or ActiveX ones? This code is for the latter. For Form checkboxes, if you want to delete them all you need is:

activesheet.checkboxes.delete

 

[awill]

Hello Andrew,

This piece of code has proved to be very useful to me and I wanted to thank-you.

However, I did have problems getting it to work (and I am a VBA starter). The problem was I had 'grouped' my checkboxes (trying to get everything lined up neatly). Can you spare a moment to explain why the code would not work and how it might have been changed to allow it to work on grouped controls?

(I am of the 'teach a man to fish' mentality. Don't just give me a fish - show me how to catch one too!)

Kindest Regards,

Gary

Also just ran into this issue. I was using the code previously to clear 22 sets of optionbuttons (66 in total). I've since grouped them by 3's in a row with a text box (for visual and convenience) so that a macro I have to hide the rows of option buttons I do not want to see, the option buttons will become hidden as well without the need to address the visible properties of each one individually via VBA (which needs to be done when not grouped because they remain visible).

Really looking forward to what the solution may be, I'm wondering if the group needs to first be addressed? That would also be kind of a headache as I have 22 lol.

Andrew, thank you for providing the original code as Gary mentioned, it's been a wonderful help!

-Andrew

 

[Steve001]

Afternoon All

I have a userform (userform1) that I have 22 "option buttons" on the form and I wish to clear the set one when I close the form.

I have used the following code form a post above and it doesn't seam to be working ?

Sub ClearAll()
    Dim box As OLEObject
    For Each box In ActiveSheet.OLEObjects
        If TypeName(box.Object) = "OptionButton" Then
            box.Object.Value = False
        ElseIf TypeName(box.Object) = "CheckBox" Then
            box.Object.Value = False
        ElseIf TypeName(box.Object) = "ListBox" Then
            box.Object.Clear
        End If
    Next box
End Sub

can somebody please advise

PS still a noob if you could put comments on please so I can understand what is happening

Steve

 

[RoryA]

That code is for controls on a worksheet, not a userform. There is no need to clear the controls on a userform when you close it - that happens automatically, unless you only hide it.

 

[Steve001]

I was only hiding it

Thanks

Steve