Announcement

Collapse
No announcement yet.

Clear all text boxes in a form with vba

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

  • 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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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
            Boo!

            Comment


            • #7
              Re: Clear all text boxes in a form with vba

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

              Comment


              • #8


                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

                Comment

                Working...
                X