+ Reply to Thread
Results 1 to 12 of 12

Clearing contents of multiple TextBox and ListBox on UserForm

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Clearing contents of multiple TextBox and ListBox on UserForm

    Hi everyone,
    I am having having problems trying to clear the contents of two ListBoxes on a Userform.

    Dim ctrlType1 As String, ctrlType2 As String
        
        ctrlType1 = "TextBox"
        ctrlType2 = "ListBox"
        
        For Each ctrl In Userform2.Controls
            If TypeName(ctrl) = ctrlType1 Then
                ctrl.Value = ""
                ElseIf TypeName(ctrl) = ctrlType2 Then
                ctrl.Clear
            End If
        Next ctrl
    It errors out on the line ctrl.Clear

    Thanks for looking into this.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    
    Dim ctrlType1 As String, ctrlType2 As String
        
       On Error Resume Next 
        For Each ctrl In Userform2.Controls
    
                ctrl.Value = ""
                ctrl.listindex = -1
    
        Next ctrl
        On Error Resume Next
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    thanks mehmetcik.
    I tried your code but it still doesn't clear the ListBoxes.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,496

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    What is the error number and error message when it errors out?

  5. #5
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Thank Rick for looking into this.
    Strangely my original code works now. I don't even know what happend. I don't remember the exact error code message, it was "Object.....".

    This works:
    Dim ctrlType1 As String, ctrlType2 As String
    
        ctrlType1 = "TextBox"
        ctrlType2 = "ListBox"
    
        For Each ctrl In Userform2.Controls
            If TypeName(ctrl) = ctrlType1 Then
                ctrl.Value = ""
                ElseIf TypeName(ctrl) = ctrlType2 Then
                ctrl.Clear
            End If
        Next ctrl
    I don't see anything different in the code.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,985

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Were you loading the Listbox using RowSource?

  7. #7
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Yes, one of the ListBox gets loaded from RowSource:

    For j = 4 To 9
          Me.LbxCat.AddItem Sheet27.Cells(j, "F")
    Next

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Place in a Module and call as required.

    Option Explicit
    Dim ctl As Control
    
    Sub Clear()
    For Each ctl In UserForm1.Controls
    Select Case TypeName(ctl)
    Case "TextBox"
    ctl.Text = ""
    Case "ComboBox", "ListBox"
    ctl.ListIndex = -1
    End Select
    Next ctl
    End Sub

  9. #9
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Thanks torachan,
    your code clears TextBoxes but not ListBoxes.

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Clearing contents of multiple TextBox and ListBox on UserForm


    On Error Resume Next
    For Each ctrl In Userform2.Controls

    ctrl.Value = ""
    ctrl.listindex = -1
    Ctrl.clear

    Next ctrl
    On Error Goto 0

  11. #11
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    Hi mehmetcik,
    On Error Resume Next
    For Each ctrl In Userform2.Controls
    
    ctrl.Value = ""
    ctrl.listindex = -1
    Ctrl.clear
    
    Next ctrl
    On Error Goto 0
    it cleared the whole userform blank. All the TextBoxes and ListBoxes are gone. That's strange.
    Last edited by Xceller; 03-13-2020 at 02:01 PM.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,985

    Re: Clearing contents of multiple TextBox and ListBox on UserForm

    You cannot use Clear if you use rowsource, it's best to load them like
    Me.ListBox1.List = Sheets("Sheet1").Range("A2:A20").Value

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filter listbox by value in textbox on userform
    By rgwood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2018, 03:45 PM
  2. [SOLVED] Userform combobox populates ListBox errors on clearing control
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2017, 04:42 PM
  3. Listbox to textbox userform
    By Okatie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2016, 03:50 PM
  4. VBA - Userform - Listbox validation and Listbox to Textbox
    By stevefisher85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2016, 07:11 AM
  5. [SOLVED] copy contents of userform listbox to another userform listbox
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2014, 11:39 AM
  6. How to Print only contents of TextBox from UserForm not whole Userform
    By khurramali2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2014, 08:39 AM
  7. [SOLVED] Clearing a textbox in userform
    By excelnut1954 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2006, 03:30 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1