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!

 

[Rick Rothstein]

I tested the code before posting it on a UserForm with two MultiSelect ListBoxes and a CommandButton.

I tested Andrew's code and it works for me also. Not that this will solve your problem (at least I don't see why it would), but I have an alternate event procedure for you to try. It is different from Andrew's in that, besides the way I test for the control being a ListBox, it does not bother to loop through all the elements setting their Selected state to False, rather I cheat... I simple convert the multi-select ListBox to a non-multi-select ListBox (which leaves no item selected) and then immediately convert it back to a mult-select ListBox so that nothing has changed from the user's point of view.

Private Sub CommandButton1_Click()
  Dim Cntrl As Object
  For Each Cntrl In Me.Controls
    If TypeOf Cntrl Is MSForms.ListBox Then
      Cntrl.MultiSelect = fmMultiSelectSingle
      Cntrl.MultiSelect = fmMultiSelectMulti
    End If
  Next
End Sub

 

[Rick Rothstein]

line 1 causes this error and it shows me line 4 ".Control" with blue background.
Note: i have 9 listboxes.

Look carefully... Andrew's code shows an "s" on the end of that property... it should be .Controls, not .Control like you show.

Also, in case our messages cross... you might miss the fact that I posted Message #11 also.

 

[noopura112]

Hello Andrew,

I must tell you that I am using Excel as front end and This lisboxes are activeX controls

 

[noopura112]

Look carefully... Andrew's code shows an "s" on the end of that property... it should be .Controls, not .Control like you show.

Also, in case our messages cross... you might miss the fact that I posted Message #11 also.

Hello Rick,

I tried .Control and .Controls both. But after long I have realized my mistake that i was using this code for ActiveX controls.

Can anybody of you tell me the modified code for ActiveX controls?

 

[Andrew Poulsom]

The code is for ActiveX controls (on a UserForm).

 

[noopura112]

Okay. Then do i need to change any listbox property? count is 1, and liststyle is option.

 

[Rick Rothstein]

Hello Andrew,

I must tell you that I am using Excel as front end and This lisboxes are activeX controls

What do you mean by the part I highlighted in red above? Are you saying your ListBoxes are on a worksheet? I ask because you said originally "I have 9 listboxes on my user form having multiselect multi property" and I note the space between the words "user" and "form"... Andrew and I have both been assuming you meant UserForm which is an Excel object on which controls are placed. Can you clarify for us exactly where your ListBoxes and CommandButton are located at.

 

[noopura112]

What do you mean by the part I highlighted in red above? Are you saying your ListBoxes are on a worksheet? I ask because you said originally "I have 9 listboxes on my user form having multiselect multi property" and I note the space between the words "user" and "form"... Andrew and I have both been assuming you meant UserForm which is an Excel object on which controls are placed. Can you clarify for us exactly where your ListBoxes and CommandButton are located at.

I am sorry if i have mentioned User Form separately. I meant to say UserForm Where we can place controls. On excel sheet i have placed 9 listboxes (From ActiveX Control group) and 2 commandButtons. From those 2 commandButtons 1 is to deselect all selected items from 9 listboxes.

I hope this time i am using correct terms :P

 

[Rick Rothstein]

I am sorry if i have mentioned User Form separately. I meant to say UserForm Where we can place controls. On excel sheet i have placed 9 listboxes (From ActiveX Control group) and 2 commandButtons. From those 2 commandButtons 1 is to deselect all selected items from 9 listboxes.

I hope this time i am using correct terms :P

No problem... I have seen some people refer to their worksheets as "forms" because they have structured them for user input and I just wanted to make sure your use of "user form" really meant UserForm (as Andrew and I have assumed). Okay, now back to your problem... in Message #16 you said...

"Then do i need to change any listbox property? count is 1, and liststyle is option."

what did you mean by "count is 1"... do you only have one item in your ListBox?

Also, did you try the code I posted in Message #11... I would be interested in the error message you get with it. Note, you can only have one CommandButton1_Click event in your UserForm's code module, so make sure to comment out Andrew's code for this test (don't delete it because you will probably need to re-activate it for further testing when Andrew comes back on line).

 

[Andrew Poulsom]

If your ListBoxes are on a worksheet try:

Private Sub CommandButton1_Click()
    Dim Ctrl As OLEObject
    Dim i As Long
    For Each Ctrl In Me.OLEObjects
        If TypeName(Ctrl.Object) = "ListBox" Then
            With Ctrl.Object
                For i = 0 To .ListCount - 1
                    .Selected(i) = False
                Next i
            End With
        End If
    Next Ctrl
End Sub