Good day,
I have a workbook where I add members to a database (Sheet name: Data) through a user form and it contains its own list box. On this user forms list box the info is on a dedicated page so my code work.
Once a member is selected from this list box it opens a new user form and a new sheet named according to the members ID. On this user form there is another list box. This list box must now display information of the active sheet that was created after selecting the member on the first user form.
The problem is that I struggle to get the correct row source
Below is my code for the list box info.
--------------------------------------------------------------------------------
Sub Refresh_lstGear()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.lstGear
.ColumnHeads = True
.ColumnCount = 4
.ColumnWidths = "50,100,200,100"
If last_row = 1 Then
.RowSource = ActiveSheet.Range("A7:D7")
Else
.RowSource = ActiveSheet.Range("A7:D") & last_row
End If
End With
End Sub
----------------------------------------------------------------------------------
Thanks in Advance
I have a workbook where I add members to a database (Sheet name: Data) through a user form and it contains its own list box. On this user forms list box the info is on a dedicated page so my code work.
Once a member is selected from this list box it opens a new user form and a new sheet named according to the members ID. On this user form there is another list box. This list box must now display information of the active sheet that was created after selecting the member on the first user form.
The problem is that I struggle to get the correct row source
Below is my code for the list box info.
--------------------------------------------------------------------------------
Sub Refresh_lstGear()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.lstGear
.ColumnHeads = True
.ColumnCount = 4
.ColumnWidths = "50,100,200,100"
If last_row = 1 Then
.RowSource = ActiveSheet.Range("A7:D7")
Else
.RowSource = ActiveSheet.Range("A7:D") & last_row
End If
End With
End Sub
----------------------------------------------------------------------------------
Thanks in Advance