Announcement

Collapse
No announcement yet.

Clear all text boxes in a form with vba

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • timc
    started a topic Clear all text boxes in a form with vba

    Clear all text boxes in a form with vba

    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

  • DynadaBithol
    replied
    Hi everyone in the group. today i have one question about MsForms in excel vba. I do not know what it mean? How to use it in proper way. So please help me to introduce about it with certain example. Thank you for your kind helping.

    Leave a comment:


  • timc
    replied
    Re: Clear all text boxes in a form with vba

    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

    Leave a comment:


  • Dave Hawley
    replied
    Re: Clear all text boxes in a form with vba

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

    Leave a comment:


  • norie
    replied
    Re: Clear all text boxes in a form with vba

    How about this:
    Code:
    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

    Leave a comment:


  • timc
    replied
    Re: Clear all text boxes in a form with vba

    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

    Leave a comment:


  • Dave Hawley
    replied
    Re: Clear all text boxes in a form with vba

    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

    Leave a comment:


  • timc
    replied
    Re: Clear all text boxes in a form with vba

    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

    Leave a comment:


  • Dave Hawley
    replied
    Re: Clear all text boxes in a form with vba

    Hi timc

    Welcome to ozgrid.com

    There a few ways this can be done. One is is like
    Code:
    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
    Code:
    Private Sub CommandButton1_Click()
    Unload Me
    UserForm1.Show
    End Sub

    Leave a comment:

Working...
X