How to empty all comboboxes and textboxes in a userform

**LoveCandle** · 05-03-2009, 03:50 AM

Hi everyone,

I have a userform with many comboboxes and textboxes and I am using the following code to empty those controls:

Private Sub CommandButton2_Click()
Beep
If MsgBox("Are you sure you want to clear all form fields?", vbQuestion + vbYesNo, "Confirmation") = vbYes Then
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
End If
TextBox2.SetFocus
End Sub

Is there another way to clear contents of all comboboxes and textboxes instead of that one in order not edit the code in case of adding new controls.

Thanks,

**VBA Noob** · 05-03-2009, 04:21 AM

Maybe

Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then
            Ctrl.Text = ""
        End If
    Next Ctrl

VBA Noob

**LoveCandle** · 05-04-2009, 01:21 AM

Thanks alot,

That is what I am really looking for,,

**royUK** · 05-04-2009, 03:40 AM

You can simply close then open the userform

Unload Me
Load UserForm1

**LoveCandle** · 05-04-2009, 10:38 AM

What if we want to except some controls (comboboxes and textboxes) from being cleared .. what should we add to the previous code?

**royUK** · 05-04-2009, 12:17 PM

Try using the Control's Tag Property. This code will clear any control whose Tag Property is set to x

Dim oCtrl As MSForms.Control
For Each oCtrl In Me.Controls
         If oCtrl.Tag = "x" Then oCtrl.Text = ""
Next oCtrl

**LoveCandle** · 05-05-2009, 06:24 AM

Hi everyone,

first of all I would like to thank royUK for his useful tip about tag property, which I should have recognize it a long time ago in order to use it is such codes.

The latest code posted by royUK is giving the error (Object does not support this property of method) and highlight this part of the code,

oCtrl.Text = ""

What I did to get rid of that error is combining the two codes provided by royUK within one code and get the following perfect code without errors:

Dim Ctrl As Control

For Each Ctrl In Me.Controls
If (TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox") And Not Ctrl.Tag = "x" Then Ctrl.Text = ""
Next Ctrl

Thanks a lot,

**royUK** · 05-05-2009, 06:49 AM

This might be neater

  Dim Ctrl   As msforms.Control

    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "x" Then
            Select Case TypeName(Ctrl)
                Case "TextBox": Ctrl.Value = ""
                Case "ComboBox": Ctrl.ListIndex = -1
            End Select
        End If
    Next Ctrl

**LoveCandle** · 05-06-2009, 01:36 AM

your way is really neater,

Thanks a lot,

**royUK** · 05-06-2009, 02:28 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

How to empty all comboboxes and textboxes in a userform

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Re: How to empty all comboboxes and textboxes in a userform

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions