Clear all text boxes in a form with vba

[timc]

Hi, does anyone know how to clear all the text boxes in a form using vba??
Once i have pasted collected information to a spreadsheet, I want to clear around 100 text boxes before collecting the next round of information.

Cheers

[Dave Hawley]

Hi timc

Welcome to ozgrid.com

There a few ways this can be done. One is is like

Private Sub CommandButton1_Click() 
    Dim cControl As Control 
     
    For Each cControl In Me.Controls 
        If cControl.Name Like "Text*" Then cControl = vbNullString 
    Next 
End Sub 

The other, if the default of the Textboxes is nothing and other controls don't matter, is

Private Sub CommandButton1_Click() 
    Unload Me 
    UserForm1.Show 
End Sub 

[timc]

Hi Dave,

Thanks very much. I have used the second option and it works a treat. I played around with the first option as it offered potential for other things I would like to do, but unfortunately I had named all my text boxes, so I couldnt use.

I appreciate the assistance.

Cheers

[Dave Hawley]

A good way to retsrict the loop to specific Controls it to place some text into the Tag Property (at design time) then use code like;

If cControl.Tag Like "Opt1" Then

[timc]

Thanks Dave,

Excellent tip. I am incorporating into another form I am developing. This is a tremendous forum. Thanks again for your time.

Cheers
Tim

[norie]

How about this:

Private Sub CommandButton1_Click() 
    Dim ctl 
    For Each ctl In Me.Controls 
        If TypeOf ctl Is msforms.TextBox Then 
            ctl.Text = "" 
        End If 
    Next ctl 
End Sub 

[Dave Hawley]

Arrh yes, TypeOf. Had forgotten about that. Nice one!

[timc]

Thanks Norie, that works a treat. It is a little smoother (less flicker) than the unload Me approach and UserForm.Show I had been using.

Cheers