Solved: set ListBox row source automatically for each sheet

[simora]

I have vba code which loads the names of the sheets in a workbook into a Combobox.
This ComboBox helps to populate a ListBox based on the ComboBox selection.
Right now, I can use Select / Case or a bunch of If Then statements to set the Rowsource for each ListBox based on which sheet is selected in the ComboBox, like this:

[VBA]With Sheet1
ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)
End With[/VBA]

What I'll like to know is how do I set the ListBox1.RowSource automatically if another sheet is added by the user, in the same way that I have the ComboBox automatically populated in the Private Sub UserForm_Initialize module, like this:

For Each ws In ThisWorkbook.Worksheets
          ComboBox1.AddItem ws.Name
         Next ws

The Rowsource for each sheet in the listbox is the same range on each sheet.

ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)

Any code or suggestions welcome

Thanks

[GTO]

Could you post a small example wb? It sounds doable, but leastwise for me, I'm not sure how the combo box selection is filling the listbox.

[GTO]

Sorry - brain fade I think. Try:
[vba]
Private Sub ComboBox1_Change()
With Worksheets(ComboBox1.Value)
ListBox1.RowSource = Range(.Cells(4, 1), .Cells(Rows.Count, 1).End(xlUp)).Address(, , , True)
End With
End Sub
[/vba]
Mark

[simora]

Not sure I did this correctly. I'm trying to post a sample workbook.

[simora]

Mark:
Thanks!
I'll try your code right now.

[simora]

Hi Mark:

Your code works as you said. Great!

Thanks.

[lucas]

Your new sheets don't show in the combox when added unless you unload the userform and re-open it.

Also, Adding a new sheet each time with the same name will cause problems too.

You can incriment the sheet name by counting the sheets and adding a number.

[simora]

Lucas:

Thanks!
Actually, the new worksheet will be getting its name from TextBox2, and I'll look into displaying a Quick Splash screen thanking the user for adding the new Worksheet. Still working out the kinks.
See the attached work in Progress.

[lucas]

I see simora, I just noticed a few things and thought I would point them out.......

[simora]

Appreciated the heads up and comments.

As usual, you guys were a great help.

Thanks a million.

[GTO]

I am glad that we were able to help you. That said - you started this thread at 1958 hrs and another thread at http://www.mrexcel.com/forum/showthread.php?t=439804 at 2016 hrs.

Please read HERE

Certainly you realize that posting in two different sites without advising in each of the parallel thread, means that some of the helpers end up wasting time in duplicating ea others' efforts.


Reference Steve's good points as to adding a sheet with the userform already loaded, not well tested, but I was thinking that something like:

In ThisWorkbook Module:

Dim OldShName As String
    
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim wks As Worksheet
    
    On Error Resume Next
    Set wks = ThisWorkbook.Worksheets(OldShName)
    On Error GoTo 0
    
    If wks Is Nothing _
    And UF_IsLoaded("UserForm1") Then
        Call ComboRepopulate
    End If
End Sub
    
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    OldShName = Sh.Name
End Sub
    
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
    If UF_IsLoaded(UserForm1.Name) Then
        Call ComboRepopulate
    End If
End Sub
    
Private Sub ComboRepopulate()
Dim ws As Worksheet
    
        UserForm1.ComboBox1.Clear
        For Each ws In ThisWorkbook.Worksheets
            UserForm1.ComboBox1.AddItem ws.Name
        Next ws
End Sub
    
Private Function UF_IsLoaded(UFName As String) As Boolean
Dim UF As Object
    
    For Each UF In UserForms
        If UCase(UFName) = UCase(UF.Name) Then
            UF_IsLoaded = True
            Exit For
        End If
    Next
End Function

Mark