Consulting

Results 1 to 14 of 14

Thread: Populate ListBox from Multiple sheets

  1. #1
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location

    Talking Populate ListBox from Multiple sheets

    Hi All-
    I am working on Userform to enter data to multiple worksheets and I was able to get that work. But, I want to take it further by being able to update previous entries through the Userform.

    I have a Listbox that I am trying to Populate from all the worksheet and then select the entry and have the Textbox and combobox fill in by selecting an entry in the list. And be able to update and save new information.

    After looking around I am able to populate the Listbox from a Single sheet but not from multiple sheets!
    This could be impossible but, wanted to see someone had a solution!

    Attached file and Code below:
    Log-01.xlsm


    Private Sub Commandbutton1_Click()
    Dim cNum As Integer
    Dim X As Integer
    Dim nextrow As Range
    Dim sht As String
    'set the variable for the sheet
    sht = ComboBox1.Value
    'check for values
    If Me.ComboBox1.Value = "" Then
    MsgBox "Select a sheet from the combobox and add the date"
    Exit Sub
    End If
    'change the number for the number of controls on the userform
    cNum = 4
    'add the data to the selected worksheet
    Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    For X = 1 To cNum
    nextrow = Me.Controls("Reg" & X).Value
    Set nextrow = nextrow.Offset(0, 1)
    Next
    'clear the values in the userform
    For X = 1 To cNum
    Me.Controls("Reg" & X).Value = ""
    Next
    'communicate the results
    MsgBox "The values have been sent to the " & sht & " sheet"
    
    
    End Sub
    
    
    Private Sub commandbutton2_Click()
    'close the userform
    Unload Me
    End Sub
    
    
    Private Sub Label8_Click()
    
    
    End Sub
    
    
    Private Sub ListBox1_Click()
    Dim ws As Worksheet
    
    
    Dim say As Long, A As Byte
    
    
    For A = 0 To 11
    Controls("textbox" & A + 1) = ListBox1.Column(A)
    
    
    Next
    
    
    Sheets("div01").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.Row
    Sheets("div01").Range("A" & say & ":L" & say).Select
    'TextBox15 = ListBox1.ListIndex + 1
    End Sub
    
    
    Private Sub UserForm_Initialize()
    'dim the variables
    Dim ws As Worksheet
    'loop through worksheets
    For Each ws In Worksheets
    'use the code name in case sheet name changes
    Select Case ws.CodeName
    'exclude these sheets by code name
    Case "Sheet1"
    'Add the rest
    Case Else
    Me.ComboBox1.AddItem ws.Name
    End Select
    Next ws
    
    
    ListBox1.ColumnWidths = "100;85;85;80"         'COLUMN WITH OF LISTBOX
    ListBox1.ColumnCount = 4                                                'COLUMN NUMBER OF LISTBOX
    ListBox1.List = Sheets("div01").Range("A3:l" & [a65536].End(3).Row).Value
    
    
    End Sub

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    add this at the bottom where you initialise the list:

    ListBox1.ColumnWidths = "100;85;85;80"         'COLUMN WITH OF LISTBOXListBox1.ColumnCount = 4                                                'COLUMN NUMBER OF LISTBOX
    ListBox1.List = Sheets("div01").Range("A3:l" & [a65536].End(3).Row).Value
    inarr = Sheets("div02").Range("A3:l" & [a65536].End(3).Row).Value
    With ListBox1
    For i = 1 To UBound(inarr)
    
    
    .AddItem
    .List(i, 0) = (inarr(i, 1))
    .List(i, 1) = (inarr(i, 2))
    .List(i, 2) = (inarr(i, 3))
    .List(i, 3) = (inarr(i, 4))
    Next i
    End With
    you could do all three sheets like that, in a loop

  3. #3
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Hi Offthelip,
    Thank you very much for assistance with this. I tried the above code for all four sheets but, only the first and last sheet show up in the list box. Also, this is strange but for the first sheet, the only single row appears (I added additional rows to test). Part of the updated code below:

    ListBox1.ColumnWidths = "100;85;85;80;50"         'COLUMN WIdTH IN LISTBOX
    ListBox1.ColumnCount = 4                       'COLUMN NUMBER OF LISTBOX
    ListBox1.List = Sheets("div01").Range("A3:l" & [a65536].End(3).Row).Value
    inarr = Sheets("div02").Range("A3:l" & [a65536].End(3).Row).Value
    inarr = Sheets("div03").Range("A3:l" & [a65536].End(3).Row).Value
    inarr = Sheets("div04").Range("A3:l" & [a65536].End(3).Row).Value
    With ListBox1
        For i = 1 To UBound(inarr)
        
             
            .AddItem
            .List(i, 0) = (inarr(i, 1))
            .List(i, 1) = (inarr(i, 2))
            .List(i, 2) = (inarr(i, 3))
            .List(i, 3) = (inarr(i, 4))
        Next i
    End With

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Untested.
    ListBox1.ColumnWidths = "100;85;85;80;50"  'COLUMN WIdTH IN LISTBOX
    ListBox1.ColumnCount = 4  'COLUMN NUMBER OF LISTBOX
    z = 1
    For j = 1 To 4
      inarr = Sheets("div" & Format(j, "00")).Range("A3:l" & [a65536].End(3).Row).Value
      With ListBox1
        For i = 1 To UBound(inarr)
          .AddItem
          .List(z, 0) = (inarr(i, 1))
          .List(z, 1) = (inarr(i, 2))
          .List(z, 2) = (inarr(i, 3))
          .List(z, 3) = (inarr(i, 4))
          z = z + 1
        Next i
      End With
    Next j
    ?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Hi p45cal-
    I just gave it go and got a run-time error: 'Could not set List Property. Invalid property array index.'

    -Nimesh

  6. #6
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    the problem is the the J index there is no "DIV04" sheet .
    I also spotted that it addes a load of blanks between each sheet, so I added an exit loop if blank

    ListBox1.ColumnWidths = "100;85;85;80;50" 'COLUMN WIdTH IN LISTBOXListBox1.ColumnCount = 4 'COLUMN NUMBER OF LISTBOX
    Z = 1
    For j = 1 To 3  ' changed to 3  instead of 4
        inarr = Sheets("div" & Format(j, "00")).Range("A3:l" & [a65536].End(3).Row).Value
        With ListBox1
            For i = 1 To UBound(inarr)
                If inarr(i, 1) = "" Then  ' added to get rid of the blank lines.
                 Exit For
                End If
                .AddItem
                .List(Z, 0) = (inarr(i, 1))
                .List(Z, 1) = (inarr(i, 2))
                .List(Z, 2) = (inarr(i, 3))
                .List(Z, 3) = (inarr(i, 4))
                Z = Z + 1
            Next i
        End With
    Next j
    End Sub

  7. #7
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Offthlip-
    I just gave this try but, nothing same error.
    I have attached the file with new code.

    Log-01a.xlsm

    -nimesh

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    One can use a method as p45cal did or an array. If you want to use the former, please post back.

    For the latter, install the BAS file as commented.
    Private Sub UserForm_Initialize()  
      Dim ws As Worksheet, a, b, r As Range
      
      'loop through worksheets
      For Each ws In Worksheets
        Set r = ws.Range("A3:C" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        'use the code name in case sheet name changes
        Select Case ws.CodeName
        'exclude these sheets by code name
        Case "Sheet1"
        'Add the rest
        Case Else
          ComboBox1.AddItem ws.Name
          If Not IsArray(a) Then
            a = r
            Else
            b = r
            'www.cpearson.com/excel/VBAArrays.htm
            a = CombineTwoDArrays(a, b)
          End If
        End Select
      Next ws
      
      ListBox1.ColumnWidths = "100;85;85;80;50" 'COLUMN WIdTH IN LISTBOX
      ListBox1.ColumnCount = 3 'COLUMN NUMBER OF LISTBOX
      ListBox1.List = a
    End Sub

  9. #9
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    this works:

    ListBox1.ColumnWidths = "100;85;85;80" 'COLUMN WITH OF LISTBOXListBox1.ColumnCount = 4                                                'COLUMN NUMBER OF LISTBOXListBox1.List = Sheets("div01").Range("A3:l" & [a65536].End(3).Row).Value
    inarr = Sheets("div02").Range("A3:l" & [a65536].End(3).Row).Value
    With ListBox1
        For i = 1 To UBound(inarr)
             
             
            .AddItem
            .List(i, 0) = (inarr(i, 1))
            .List(i, 1) = (inarr(i, 2))
            .List(i, 2) = (inarr(i, 3))
            .List(i, 3) = (inarr(i, 4))
        Next i
    End With
    inarr = Sheets("div03").Range("A3:l" & [a65536].End(3).Row).Value
    With ListBox1
        For i = 1 To UBound(inarr)
             
             
            .AddItem
            .List(i, 0) = (inarr(i, 1))
            .List(i, 1) = (inarr(i, 2))
            .List(i, 2) = (inarr(i, 3))
            .List(i, 3) = (inarr(i, 4))
        Next i
    End With
    End Sub

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Alwasy use columA & row 1 in every sheet.
    Assign properties to userformcontrols in Design Mode.

    Private Sub UserForm_Initialize()
       For Each it In Sheets
          If it.Name <> Sheet1.Name Then it.Cells(1).CurrentRegion.Offset(1).Copy Sheet1.Cells(Rows.Count, 40).End(xlUp).Offset(1)
       Next
       
       ListBox1.List = Sheet1.Cells(2, 40).CurrentRegion.Value
       Sheet1.Cells(2, 40).CurrentRegion.ClearContents
       
       Reg4.List = Array("YES", "NO", "MAYBE")
    End Sub
    Attached Files Attached Files

  11. #11
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Thanks Kenneth Hobs.
    How would go about using the method p45cal posted?

    Nimesh-

  12. #12
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Private Sub UserForm_Initialize()  
      Dim ws As Worksheet, a, i As Long, j As Long
      
      ListBox1.ColumnWidths = "100;85;85;80;50" 'COLUMN WIdTH IN LISTBOX
      ListBox1.ColumnCount = 4 'COLUMN NUMBER OF LISTBOX
      
      'loop through worksheets
      j = 0 'Listbox item count
      For Each ws In Worksheets
        'use the code name in case sheet name changes
        Select Case ws.CodeName
        'exclude these sheets by code name
        Case "Sheet1"
        'Add the rest
        Case Else
          a = ws.Range("A3:D" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
          With ListBox1
            For i = 1 To UBound(a)
              .AddItem
              .List(j, 0) = (a(i, 1))
              .List(j, 1) = (a(i, 2))
              .List(j, 2) = (a(i, 3))
              .List(j, 3) = (a(i, 4))
              j = j + 1
            Next i
          End With
        End Select
      Next ws
    End Sub
    FWIW, I ran some speed tests. The first sheet of data, I filled to 5,000th row to show the difference. Time started at the Show and ended after the userform Activated. When looking at speed tests, IMHO, averaging can be misleading. In this case, for giggles, I averaged 10 runs in a loop. A truer average might be 10 runs of 1 each. You can see what I mean by the time for Run 1 and the average of 10.

    Of course, 8 ms versus 2 seconds is not all that much too worry about. Look at speed issues when waiting is perceived to be long.

    Run 1, 85.3266294265414 ms
    ufKen Avg: 9.31071555951278 ms

    Run 1, 1881.76474059781 ms
    ufP45cal Avg: 188.836750717818 ms


    Run 1, 1732.90580745812 ms
    ufP45cal Avg: 174.077293359643 ms

    Run 1, 66.6171870790279 ms
    ufKen Avg: 7.38832452793394 ms


    Run 1, 195.245084329126 ms
    ufSNB Avg: 20.3053194191772 ms
    Last edited by Kenneth Hobs; 09-19-2017 at 09:28 AM.

  13. #13
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Kenneth Hobs- Thank you, that did the trick.
    I didn't notice any run time speed issue, it was quick to load.

    Nimesh

  14. #14
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You probably won't until you get into 100k+ records. If I had that many, I would use Access or some other highend database Application.

    Some ask me why I don't code for speed sometimes but most any method will suffice when datasets are small. My crystal ball is broken right now so I don't always make the "right" guess.

Tags for this Thread

Posting Permissions

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