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]

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).

I think I misread your answer when I wrote the above. In re-reading your message I see you said... "On excel sheet i have placed 9 listboxes (From ActiveX Control group) and 2 commandButtons". My confusion was over when you said "I meant to say UserForm Where we can place controls". A worksheet is not the same thing as a UserForm... a UserForm is an object that can contain controls and be shown as a floating window over top of a worksheet... think of it like it was a specialized MessageBox that the programmer can design and layout. I realize now that your controls are on the worksheet itself. Andrew has given you his original code modified to handle the controls being on the worksheet in Message #20. Here is my original code modified for your controls being located directly on the worksheet...

Private Sub CommandButton1_Click()
  Dim Ctrl As OLEObject
  Dim i As Long
  For Each Ctrl In Me.OLEObjects
    If TypeName(Ctrl.Object) = "ListBox" Then
      Ctrl.Object.MultiSelect = fmMultiSelectSingle
      Ctrl.Object.MultiSelect = fmMultiSelectMulti
    End If
  Next
End Sub

 

[Andrew Poulsom]

What if the ListBox's MultiSelect property was originally set to fmMultiSelectExtended Rick?

 

[Rick Rothstein]

What if the ListBox's MultiSelect property was originally set to fmMultiSelectExtended Rick?

I assumed that when the OP said "I have 9 listboxes on my user form having multiselect multi property" in her original message, she meant fmMultiSelectMulti for the MultiSelect property. However, if the ListBoxes were set to fmMultiSelectExtended, then I would change this line of code in my event procedure...

Ctrl.Object.MultiSelect = fmMultiSelectMulti

to this...

Ctrl.Object.MultiSelect = fmMultiSelectExtended

If your question was meant to suggest that some ListBoxes might be in fmMultiSelectMulti mode whereas others might be in fmMultiSelectExtended mode, then I would change my suggested code to this instead...

Private Sub CommandButton1_Click()
  Dim Ctrl As OLEObject, OriginalMode As Long
  For Each Ctrl In Me.OLEObjects
    If TypeName(Ctrl.Object) = "ListBox" Then
      OriginalMode = Ctrl.Object.MultiSelect
      Ctrl.Object.MultiSelect = fmMultiSelectSingle
      Ctrl.Object.MultiSelect = OriginalMode
    End If
  Next
End Sub

 

[Andrew Poulsom]

It was the robustness of your revised code that I was after. I knew what change was needed, of course. I just wanted to preempt that question from the OP.

 

[Rick Rothstein]

It was the robustness of your revised code that I was after.

Yes, I understand your posting "style".

I knew what change was needed, of course.

Yes, I am well aware that you already knew that... I know your programming skills as well as your posting style.

I just wanted to preempt that question from the OP.

At the time, I did not think there would be a follow-up question by the OP given her "multiselect multi property" statement; but then again, given the whole "user form", UserForm, worksheet location confusion, maybe that assumption was not really a warranted one after all.

 

[noopura112]

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).

Hello Rick,

I am sorry for late reply. I tried your code. I am getting same error " Method or data member not found".
Count is column Count property of listbox. There are many items in the listboxes.
I tried Andrew's new code. But that is giving me neither error nor output.

 

[Andrew Poulsom]

I tested my code before posting, so I don't know why it isn't working for you.

 

[noopura112]

It was the robustness of your revised code that I was after. I knew what change was needed, of course. I just wanted to preempt that question from the OP.

I am Sorry Andrew for late reply. I was out of town. Checking all messages now. I tried your code in message #20. I am sure it is correct but I do not know what's the problem, it neither gives me error nor output.

 

[noopura112]

I think I misread your answer when I wrote the above. In re-reading your message I see you said... "On excel sheet i have placed 9 listboxes (From ActiveX Control group) and 2 commandButtons". My confusion was over when you said "I meant to say UserForm Where we can place controls". A worksheet is not the same thing as a UserForm... a UserForm is an object that can contain controls and be shown as a floating window over top of a worksheet... think of it like it was a specialized MessageBox that the programmer can design and layout. I realize now that your controls are on the worksheet itself. Andrew has given you his original code modified to handle the controls being on the worksheet in Message #20. Here is my original code modified for your controls being located directly on the worksheet...

Private Sub CommandButton1_Click()
  Dim Ctrl As OLEObject
  Dim i As Long
  For Each Ctrl In Me.OLEObjects
    If TypeName(Ctrl.Object) = "ListBox" Then
      Ctrl.Object.MultiSelect = fmMultiSelectSingle
      Ctrl.Object.MultiSelect = fmMultiSelectMulti
    End If
  Next
End Sub

I am trying your code.
For all lisboxes Multiselect property is multi which i must set to "multi" only.

 

[Andrew Poulsom]

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