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


                • #9


                  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.

                  Comment

                  Working...
                  X