fixing code rowsource in listbox through all sheets

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
115
Office Version
  1. 2016
Platform
  1. Windows
hi
I have this code to show dat in listbox a based on active sheet.
VBA Code:
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

Code:
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 ?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,836
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Active Member
Joined
Nov 11, 2020
Messages
494
Office Version
  1. 2016
Platform
  1. Windows
If you want to display data from each worksheet one by one in the listbox try this...
VBA Code:
Private Sub UserForm_Initialize()
   
    Application.OnTime Now + TimeValue("00:00:00"), "WsLoop"

End Sub
This goes to the standard module...
VBA Code:
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

Board Regular
Joined
Apr 8, 2021
Messages
115
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Jul 14, 2010
Messages
1,836
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
@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

Active Member
Joined
Nov 11, 2020
Messages
494
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Here is example...
VBA Code:
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,836
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Here is example...
VBA Code:
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

Board Regular
Joined
Apr 8, 2021
Messages
115
Office Version
  1. 2016
Platform
  1. Windows
@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

Active Member
Joined
Nov 11, 2020
Messages
494
Office Version
  1. 2016
Platform
  1. Windows
I'm glad you like this way.
If you want to add some options you can try in this way.
VBA Code:
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
 

Forum statistics

Threads
1,141,293
Messages
5,705,532
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top