PDA

View Full Version : Solved: set ListBox row source automatically for each sheet



simora
01-07-2010, 07:58 PM
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:

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

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
01-07-2010, 08:08 PM
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
01-07-2010, 08:25 PM
Sorry - brain fade I think. Try:

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

Mark

simora
01-07-2010, 08:47 PM
Not sure I did this correctly. I'm trying to post a sample workbook.

simora
01-07-2010, 08:51 PM
Mark:
Thanks!
I'll try your code right now.

simora
01-07-2010, 09:14 PM
Hi Mark:

Your code works as you said. Great!

Thanks.

lucas
01-07-2010, 09:25 PM
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
01-07-2010, 09:57 PM
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
01-07-2010, 10:06 PM
I see simora, I just noticed a few things and thought I would point them out.......

simora
01-07-2010, 10:11 PM
Appreciated the heads up and comments.

As usual, you guys were a great help.

Thanks a million.

GTO
01-07-2010, 11:25 PM
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 (http://www.excelguru.ca/node/7)

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