fixing code rowsource in listbox through all sheets

[Hasson]

hi
I have this code to show dat in listbox a based on active sheet.

Private Sub UserForm_Initialize()
   MyRowSource = "A2:d" & Str(activesheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
    MyRowSource = Replace(MyRowSource, " ", "")
    ListBox1.RowSource = MyRowSource
    Next
End Sub

I try showing data in list box throught all sheets but so far not work

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

   MyRowSource = "A2:d" & Str(ws.Cells.SpecialCells(xlCellTypeLastCell).Row)
    MyRowSource = Replace(MyRowSource, " ", "")
    ListBox1.RowSource = MyRowSource
    Next
End Sub

any help to fix it ?

 

[bobsan42]

First, you should include the sheet name in the rowsource like this: Sheet1!A2:D100

Second I don't se the sence in the initialize code. You loop through several sheets and each time replace the rowSource ?!? It's not like you are trying to combine the data from all sheets.
Could explain clearly what exactly you are trying to achieve here?

 

[EXCEL MAX]

If you want to display data from each worksheet one by one in the listbox try this...

Private Sub UserForm_Initialize()
   
    Application.OnTime Now + TimeValue("00:00:00"), "WsLoop"

End Sub

This goes to the standard module...

Dim ws As Worksheet
Dim MyRowSource As String

Sub WsLoop()

     For Each ws In ThisWorkbook.Worksheets
        Application.Wait Now + TimeValue("00:00:01")
        MyRowSource = ws.Name & "!" & "A2:D" & _
                    CStr(ws.Cells.SpecialCells(xlCellTypeLastCell).Row)
        frmExample.ListBox1.RowSource = MyRowSource
        DoEvents
     Next

End Sub

 

[Hasson]

@bobsan42

you are trying to combine the data from all sheets.

yes this is what i want

 

[Hasson]

@EXCEL MAX

If you want to display data from each worksheet one by one in the listbox try this...

absolutely no
I would show all of data from all sheets like merge data under each other of them

 

[bobsan42]

@bobsan42

yes this is what i want

This is certainly NOT the way
You can't use Row source in this case.
Instead, you have to add all data from the sheets to an array and then assign it to the LIST property of the listbox.
I would prefer to have all data in one place instead of collecting it from all sheets.

 

[EXCEL MAX]

Here is example...

Private Sub UserForm_Initialize()
    
    Dim ws As Worksheet, vR As Long, vRAll As Long, vA(), vN As Long, vC As Long
    
'calculate the final size
    For Each ws In Worksheets
         vR = ws.Cells(Rows.Count, "A").End(xlUp).Row
         vRAll = vRAll + vR
    Next ws
'resize array
    ReDim vA(1 To vRAll, 1 To 4)
'fill array
    vC = 1
    For Each ws In Worksheets
        vR = ws.Cells(Rows.Count, "A").End(xlUp).Row
        For vN = 2 To vR
            vA(vC, 1) = ws.Cells(vN, 1)
            vA(vC, 2) = ws.Cells(vN, 2)
            vA(vC, 3) = ws.Cells(vN, 3)
            vA(vC, 4) = ws.Cells(vN, 4)
            vC = vC + 1
        Next vN
    Next ws
'display in the listbox
    ListBox1.ColumnCount = 4
    ListBox1.List() = vA
    
 End Sub

 

[bobsan42]

Here is example...

Private Sub UserForm_Initialize()
   
    Dim ws As Worksheet, vR As Long, vRAll As Long, vA(), vN As Long, vC As Long
   
'calculate the final size
    For Each ws In Worksheets
         vR = ws.Cells(Rows.Count, "A").End(xlUp).Row
         vRAll = vRAll + vR
    Next ws
'resize array
    ReDim vA(1 To vRAll, 1 To 4)
'fill array
    vC = 1
    For Each ws In Worksheets
        vR = ws.Cells(Rows.Count, "A").End(xlUp).Row
        For vN = 2 To vR
            vA(vC, 1) = ws.Cells(vN, 1)
            vA(vC, 2) = ws.Cells(vN, 2)
            vA(vC, 3) = ws.Cells(vN, 3)
            vA(vC, 4) = ws.Cells(vN, 4)
            vC = vC + 1
        Next vN
    Next ws
'display in the listbox
    ListBox1.ColumnCount = 4
    ListBox1.List() = vA
   
 End Sub

You can speed up (possibly a lot, depending on the range size) the code by reading the complete range in each sheet to a temporary array in one go.
Then transfer the values from it to vA.

 

[Hasson]

@EXCEL MAX that's great ! and I have multiple options buttons . each option button link with sheet so when I run the macro will shows all of data in all of sheets as your code does it but I want when select any option button should show specific the sheet . the option button 's name is the same thing as in sheet names (sh1,fgj1,zxc)

 

[EXCEL MAX]

I'm glad you like this way.
If you want to add some options you can try in this way.

Dim ws As Worksheet, vR As Long, vA(), vN As Long, vC As Long

Private Sub UserForm_Initialize()
 
    Call AllRows
 
End Sub
 
Sub AllRows()
  
    Dim vRAll As Long
'calculate the final size
    For Each ws In Worksheets
         vR = ws.Cells(Rows.Count, "A").End(xlUp).Row - 1
         vRAll = vRAll + vR
    Next ws
'resize array
    ReDim vA(1 To vRAll, 1 To 4)
'fill array
    vC = 1
    For Each ws In Worksheets
        vR = ws.Cells(Rows.Count, "A").End(xlUp).Row
        For vN = 2 To vR
            vA(vC, 1) = ws.Cells(vN, 1)
            vA(vC, 2) = ws.Cells(vN, 2)
            vA(vC, 3) = ws.Cells(vN, 3)
            vA(vC, 4) = ws.Cells(vN, 4)
            vC = vC + 1
        Next vN
    Next ws
'display in the listbox
    ListBox1.ColumnCount = 4
'clear row source before display
    ListBox1.RowSource = ""
    ListBox1.List() = vA
  
End Sub

Private Sub sh1_Change()
  
     vR = Sheets("sh1").Cells(Rows.Count, "A").End(xlUp).Row
     ListBox1.RowSource = "sh1!A2:D" & vR
  
End Sub

Private Sub fgj1_Change()
  
    vR = Sheets("fgj1").Cells(Rows.Count, "A").End(xlUp).Row
    ListBox1.RowSource = "fgj1!A2:D" & vR
  
End Sub

Private Sub zxc_Change()

    vR = Sheets("zxc").Cells(Rows.Count, "A").End(xlUp).Row
    ListBox1.RowSource = "zxc!A2:D" & vR
  
End Sub