Solved: Resetting ComboBoxes Value to " "

[PKisielewski]

I know that you can clear Control comboboxes by doing the following:
ComboBox1.Value = " "
But I would like to clear all of the combo boxes without having to hard
code each combo box's name. I can do this for the formfields but how do you do that with the control comboboxes. I tried the following and I received a sub not defined message:

Dim j As Integer
j = 0
For Each j In ComboBox(j).Value
If ComboBox(j).Value = IsNull Then ComboBox(j) = " "
Next j
End If

Am I on the right track or am I way off. Thanks in advance for your help.

[Ken Puls]

Try this:

Dim cBox As MSForms.ComboBox
For Each cBox In Me.Controls
cBox.Clear
Next cBox

HTH,

[lucas]

This might work for you for multiple types of controls...not tested

Sub Clear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
Me.Controls(ctl.Name).Text = ""
ElseIf TypeOf ctl Is CheckBox Then
Me.Controls(ctl.Name).Value = False
ElseIf TypeOf ctl Is ComboBox Then
Me.Controls(ctl.Name).ListIndex = 0
End If
Next
End Sub

[PKisielewski]

Thanks for the reply but I received an Compile Error - Method or data member not found. It was referring to the Me.Controls.

For Each cBox In Me.Controls

When I look in the the selection dropdown when I type the Me. there is not a selection for Controls.
Still trying to figure this one out.

[PKisielewski]

Thanks for the reply. I found that in the help already and it does not work. It seems that the Me.Controls are not an option to be used in this case.
Any more suggestions. Thanks.:think:

[lucas]

It's not on a form?.....can you post your workbook?

[Norie]

Where exactly are these comboboxes located?

[PKisielewski]

I have 4 combo boxes on a Word document. I have them being reset by clicking on a command button and using this code:
ComboBox1.Value = " "
ComboBox2.Value = " "
ComboBox3.Value = " "
ComboBox4.Value = " "

But I need to not specify the name - reason being is that I am inserting new rows in tables with a new ComboBox that will be inserted and their name will change. But right now - I am not testing that part I just need to figure out how to do this without the actual name needing to be referenced. This is possible in the formfields by using an update and I just thought that surely the control comboboxes can do the same.
Thanks.

[fumei]

Ok, could you please try and be specific when you post.

1. You needed to mention that these are ActiveX controls IN the document, not on a userform. That is why code was posted using Me. That is where we normally think Controls are. Me won't work because you are not talking about a userform.

2. What does "clear" mean? Clear normally means clear everything. Combobox1.Clear removes ALL items from the list.

Is one of the items ""? Please describe the items in the combobox and how you are populating them.

In any case, ActiveX comboboxes IN a document are InlineShapes. You can deal with them as such.Sub ClearComboSelections()
Dim oInlineShape As Word.InlineShape
For Each oInlineShape In ActiveDocument.InlineShapes
If oInlineShape.OLEFormat.Object.Text <> "" Then
oInlineShape.OLEFormat.Object.Text = ""
End If
Next
End SubThis clears out the text (a selected item, OR entered text) in the ActiveX combos.

Demo attached. Type text in a combo, OR select an item. You can fire the clearing procedure from the Clear Combos icon/text on the top toolbar.

Note the combobox items are populated by the Document_Open event.

Hope this helps.

[fumei]

NOTE! The above will also empty an ActiveX textbox!

[PKisielewski]

Thanks so much that works - I changed from text to value for a combo box. One more question. If the combo box has data and I want it to just clear what is selected what do I use:

If oInlineShape.OLEFormat.Object.Value = Not Null Then

the Not Null is not working. What do I use?

Also, I will work on explaining better - thought that I did. I am not using an Userform these are just active X items in a word document. I use the Add feature on Document Open to populate the combo boxes. I want to clear out the combo boxes of only the typed in information not the add items. The users are resetting the form - clearing all the fields that they typed to reuse the same form again without having to reopen the form to do the clearing of the fields.

Again thanks so much. Look forward to hearing from you.

[Norie]

Gerry

Why not add a test to see the type of control?
Sub ClearComboSelections()
Dim oInlineShape As Word.InlineShape
For Each oInlineShape In ActiveDocument.InlineShapes
If oInlineShape.OLEFormat.Object.Text <> "" Then
If TypeName(oInlineShape.OLEFormat.Object) = "ComboBox" Then
oInlineShape.OLEFormat.Object.ListIndex = -1
End If
End If
Next
End Sub

[fumei]

Norie - Properly done, of course it should be tested.

I am getting mixed messages here. On one hand:
If the combo box has data and I want it to just clear what is selected what do I use:This states what is selected is what you want cleared - that is, one of the items added to the combobox.

On the other hand:
I want to clear out the combo boxes of only the typed in information not the add items. The users are resetting the form - clearing all the fields that they typed to reuse the same form again without having to reopen the form to do the clearing of the fields.
The definitely states you want to clear any entered text, NOT a selected item.

Make up your mind.

Further, hmmmm, I don't like it. Perhaps if you explained what it is that is going on. User enters some text, selects other items. OK...what happens between them doing that, and "reusing" the form? Why is the form being reused like this? How are going to have them fire any procedure to clear things?

[Norie]

Gerry

Eh, I didn't ask for anything.

[fumei]

Norie...huh?
Gerry

Why not add a test to see the type of control?I think I see a question mark at the end of that sentence. So...I answered. You are absolutely correct, it would be better to test.

[PKisielewski]

I still need some help please - If oInlineShape.OLEFormat.Object.Value = Not Null Then

the Not Null is not working. What do I use?

I am using the add in the Document Open to populate the lists for four combo boxes. The data in the list for 3 of them are state abbreviations, and for the fourth is yes, no, unknown. I do not want the list items to be removed they need to remain in the list so I am asking to clear the text so that when the user clicks on the reset button - the combo boxes will go back to being empty but still retain the list so that the user can select the items in the combo boxes for a new customer. This is a form that after the user enters the customer's info. they print the form and then they take the next customer's call to fill in the new information. At some point this will be in a different system and will be a real online form but for now this is what we are using.

I do want to thank you for all of your help - I did not know that the combo boxes are InlineShapes so I am learning a lot. Look forward to hearing from you and thanks again.

[PKisielewski]

Okay I got it to work - thank you Norie - I am using your code.

Anyway, I really appreciate all of the help that both you and Gerry did for me. I have learned so much in this little exercise.
:clap: