Populate ListBox from Multiple sheets

[nimesh29]

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!:banghead:
This could be impossible but, wanted to see someone had a solution!

Attached file and Code below:
20356




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

[offthelip]

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

[nimesh29]

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

[p45cal]

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
?

[nimesh29]

Hi p45cal-
I just gave it go and got a run-time error: 'Could not set List Property. Invalid property array index.'

-Nimesh

[offthelip]

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

[nimesh29]

Offthlip-
I just gave this try but, nothing same error.
I have attached the file with new code.

20371

-nimesh

[Kenneth Hobs]

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

[offthelip]

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

[snb]

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

[nimesh29]

Thanks Kenneth Hobs.
How would go about using the method p45cal posted?

Nimesh-

[Kenneth Hobs]

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

[nimesh29]

Kenneth Hobs- Thank you, that did the trick.
I didn't notice any run time speed issue, it was quick to load.

Nimesh

[Kenneth Hobs]

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. :)