+ Reply to Thread
Results 1 to 4 of 4

Create multiple records from a multiselect listbox in a userform

Hybrid View

  1. #1
    Registered User
    Join Date
    09-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2011
    Posts
    16

    Create multiple records from a multiselect listbox in a userform

    I am using this macro to collate the options selected in my userform listbox (lstName) and copy them to the next blank row on my spreadsheet (train_db)

    However, I want the values in some other items in my userform (Ent2 - Ent5) to be replicated for as many multiselect options are selected in the listbox and copied to train_db also.

    i.e. If 3 items are selected from lstName then the spreadsheet should be populated as such...

    A B C D E
    lstNameoption1 Ent2.Value Ent3.Value Ent4.Value Ent5.Value
    lstNameoption2 Ent2.Value Ent3.Value Ent4.Value Ent5.Value
    lstNameoption3 Ent2.Value Ent3.Value Ent4.Value Ent5.Value

    The current code works fine to generate new records in column A for as many lstName options are selected but I am struggling to populate columns B - E with the values from my other items.
    The code in red populates the first new row only. Can I put a simple loop on this to populate all rows equal to the number of listbox options selected, maybe?

    Private Sub cmdAdd_Click()
    'declare the variables
        Dim nextrow As Range
    
    'find the next row to add data to
        Set nextrow = Sheets("train_db").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    
    'collate all selected listbox options
        ReDim ary(0 To 0)
        With Me.lstName
        For i = 0 To .ListCount - 1
        If .Selected(i) Then
        ReDim Preserve ary(1 To UBound(ary) + 1)
        ary(UBound(ary)) = .List(i)
        End If
        Next
        End With
    
    'add values to the database
        With nextrow
        .Resize(UBound(ary)).Value = Application.Transpose(ary)
    
        .Offset(0, 1) = Ent2.Value
        .Offset(0, 2) = Ent3.Value
        .Offset(0, 3) = Ent4.Value
        .Offset(0, 4) = Ent5.Value
    
        End With
    End Sub

  2. #2
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Create multiple records from a multiselect listbox in a userform

    Can you post your workbook so we can see your list of and how your data is laid out

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Create multiple records from a multiselect listbox in a userform

    Try

    Private Sub cmdAdd_Click()
        Dim i As Long, Pointer as Long
        Dim outArray() as String
    
        With Me.lstName
            ReDim outArray(1 to .ListCount, 1 To 5)
      
            For i = 0 to .ListCount - 1
                If .Selected(i) Then
                    Pointer = Pointer + 1
                    outArray(Pointer, 1) = .List(i)
                    outArray(Pointer, 2) = Me.Ent2.Value
                    outArray(Pointer, 3) = Me.Ent3.Value
                    outArray(Pointer, 4) = Me.Ent4.Value
                    outArray(Pointer, 5) = Me.Ent5.Value
                End If
            Next i
    
            With Sheets("train_db").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                .Resize(Pointer, 5).Value = outArray
            End 
        End With
    
    End Sub
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    09-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2011
    Posts
    16

    Re: Create multiple records from a multiselect listbox in a userform

    Thank you! This is doing exactly what I wanted.

+ 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. problem vba code for multiselect listbox (userform)
    By mariec_06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2016, 10:52 AM
  2. UserForm: Enable Textbox When "Other" Selected in MultiSelect Listbox (Excel 2010)
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-02-2014, 06:40 PM
  3. [SOLVED] MultiSelect Listbox in Userform to copy data to worksheet
    By aarodn in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2013, 03:52 PM
  4. [SOLVED] listindex=0 when first item selected in a multiselect listbox on a userform?
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-17-2013, 12:38 PM
  5. Multiselect Listbox to select multiple worksheets
    By Alagu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2012, 01:28 AM
  6. Unable to Detect When UserForm MultiSelect ListBox Selection Changes
    By ShortSword in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-07-2012, 10:49 PM
  7. userform multiselect listbox problem
    By apndas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2006, 11:12 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