+ Reply to Thread
Results 1 to 4 of 4

Loop through Userform Controls and assign value control to cell

Hybrid View

peejayjay Loop through Userform... 03-09-2017, 11:56 AM
Bernie Deitrick Re: Loop through Userform... 03-09-2017, 04:42 PM
peejayjay Re: Loop through Userform... 03-12-2017, 04:29 AM
Bernie Deitrick Re: Loop through Userform... 03-12-2017, 01:56 PM
  1. #1
    Registered User
    Join Date
    02-22-2017
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    9

    Loop through Userform Controls and assign value control to cell

    I'm using Excel 2010

    I'm trying to loop through an UserForm and access all the values selected in each control. The userform contains ComboBoxes, TextBoxes, and ListBoxes. Multiselect function is enabled in all listboxes.
    i = 1
    For Each ctrl In UserForm1.Controls
         If TypeName(ctrl) <> "ListBox" Then
             ws.Cells(2, i) = ctrl.Value
         End If
         i = i + 1
    Next ctrl
    It seems that I can't access the value of each control using ctrl.Value

    Each control will have its value written on a new column on the same row. Each ListBox with multiple items will have all its selected items written down the same column on new rows.

    EDIT:
    I realized that values can be access with ctrl.Value for TextBox and ComboBox. I need help to access values selected on ListBoxes
    Last edited by peejayjay; 03-09-2017 at 12:05 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,391

    Re: Loop through Userform Controls and assign value control to cell

    To check listboxes, use code like

        With UserForm1.ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    ws.Cells(3, i+1) = .List(i)
                End If
            Next i
        End With
    Since you allow multi-select, you will either need to use one row for each listbox, or keep track of the count of selected items to use with an offset to prevent overwriting.
    Last edited by Bernie Deitrick; 03-09-2017 at 04:44 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    02-22-2017
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    9

    Re: Loop through Userform Controls and assign value control to cell

    Quote Originally Posted by Bernie Deitrick View Post
    To check listboxes, use code like

        With UserForm1.ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    ws.Cells(3, i+1) = .List(i)
                End If
            Next i
        End With
    Since you allow multi-select, you will either need to use one row for each listbox, or keep track of the count of selected items to use with an offset to prevent overwriting.
    how can i modify the code to loop through 10 listboxes?
    each new listbox will go on a new column, and each selected value will go down the row

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,391

    Re: Loop through Userform Controls and assign value control to cell

    Try code like this - not sure how you are indexing through the cells, so make changes there...

        Dim ctrlT As Control
        Dim lngC As Long
    
        lngC = 5 'First column for the listbox results
    
        For Each ctrlT In Me.Controls
            If TypeName(ctrlT) = "ListBox" Then
                With ctrlT
                    For i = 0 To .ListCount - 1
                        If .Selected(i) = True Then
                            ws.Cells(i + 2, lngC) = .List(i)
                        End If
                    Next i
                End With
                lngC = lngC + 1
            End If
        Next ctrlT

+ 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. [SOLVED] May we loop over an event of userform controls
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2017, 10:41 AM
  2. [SOLVED] Loop through userform frame controls
    By c00ly81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2016, 04:38 AM
  3. [SOLVED] adapt userform loop using control name containing variable for worksheet activex controls
    By bmeister in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-22-2014, 02:45 PM
  4. [SOLVED] How to reference Controls in a Userform based on the control's tabindex instead of name?
    By bmxfreedom in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-05-2013, 02:27 PM
  5. Replies: 5
    Last Post: 02-27-2013, 12:04 PM
  6. Replies: 1
    Last Post: 10-12-2012, 04:36 AM
  7. Loop through controls in UserForm
    By JezLisle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2009, 05:38 AM

Tags for this Thread

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