Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Clear all text boxes in a form with vba

  1. #1
    Join Date
    19th November 2004
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    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
    VB:
    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
    VB:
    Private Sub CommandButton1_Click() 
        Unload Me 
        UserForm1.Show 
    End Sub 
    
    

  3. #3
    Join Date
    19th November 2004
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    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

  5. #5
    Join Date
    19th November 2004
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Clear all text boxes in a form with vba

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Clear all text boxes in a form with vba

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

  8. #8
    Join Date
    19th November 2004
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Command Button to clear check boxes
    By arandles in forum EXCEL HELP
    Replies: 2
    Last Post: June 26th, 2006, 14:59
  2. Loop through text boxes on User Form
    By AUSSIECOMM in forum EXCEL HELP
    Replies: 2
    Last Post: May 17th, 2005, 23:07
  3. User Form - Displaying Values in Text Boxes
    By Neal in forum EXCEL HELP
    Replies: 4
    Last Post: May 17th, 2003, 13:28
  4. Replies: 6
    Last Post: April 4th, 2003, 21:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno