Pass SheetName in ListBox RowSource-SheetName Varies

[MSingh]

Hi,

My workbook has several sheets with Lisbox1 bound to RowSource.
How can i pass the active sheet name in the RowSource so that ListBox1 is populated
with the data of the active sheet.


Private Sub UserForm_Initialize()

'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 7
.ColumnHeads = True
.ColumnWidths = "150 pt;100 pt;75 pt;55 pt;100 pt;120 pt;120 pt"
.TextColumn = True
.RowSource = "FNB_Payees1!A2:I" & xlLastRow("FNB_Payees1")
.ListStyle = fmListStyleOption
.ListIndex = 0
End With
End Sub

Function xlLastRow(Optional WorksheetName As String) As Long
' By Brandtrock
' 'Check for optional worksheetname else use activesheet
If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If

' find the last populated row in a worksheet
With Worksheets(WorksheetName)
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With

End Function

Thanks again
Mohamed

[HansV]

Change the line

.RowSource = "FNB_Payees1!A2:I" & xlLastRow("FNB_Payees1")

to

.RowSource = "A2:I" & xlLastRow

[MSingh]

Hi,

Thanks Hans - u make things look so simple.
Greatly appreciated!

Mohamed