Dynamic listbox

[mmn1000]

Hi,
How can I create a listbox in the userform?
that when I was in any sheet, it would show me the information of the same sheet.

 

[Legacy 143009]

You can use ActiveSheet reference to refer to the current active sheet. This example will populate items from A1 to A5, from the sheet which is currently selected.

Private Sub UserForm_Initialize()
  ListBox1.List = ActiveSheet.Range("A1:A5").Value2
End Sub

 

[mmn1000]

۰۰ can use ActiveSheet reference to refer to the current active sheet. This example will populate items from A1 to A5, from the sheet which is currently selected.

Private Sub UserForm_Initialize()
  ListBox1.List = ActiveSheet.Range("A1:A5").Value2
End Sub

Thank you for your guide
My limits in each sheet are tables, should it be done in the same way for tables?

 

[Legacy 143009]

Somehow you have to address your tables. For example this will select first table in actively selected sheets:

Activesheet.listobjects(1)

 

[mmn1000]

Somehow you have to address your tables. For example this will select first table in actively selected sheets:

Activesheet.listobjects(1)

Hi
This code did not work for me

 

[Legacy 143009]

Please find the sample file:

https://file.io/01aY9tA36cSE

Private Sub UserForm_Initialize()
  ListBox1.List = ActiveSheet.ListObjects(1).DataBodyRange.Value2
End Sub

 

[Mario_R]

Hello. See if the following is what you are looking for:

Test_2.xlsm​

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim k
k = Application.Match(Sh.Name, Array("Sheet1", "Sheet2"), 0)
If Not IsError(k) Then UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
  ListBox1.RowSource = ActiveSheet.ListObjects(1).Name
End Sub

 

[mmn1000]

Hello. See if the following is what you are looking for:

Test_2.xlsm​

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim k
k = Application.Match(Sh.Name, Array("Sheet1", "Sheet2"), 0)
If Not IsError(k) Then UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
  ListBox1.RowSource = ActiveSheet.ListObjects(1).Name
End Sub

Hi,
Thank you for your beautiful answer

 

[mmn1000]

Please find the sample file:

https://file.io/01aY9tA36cSE

Private Sub UserForm_Initialize()
  ListBox1.List = ActiveSheet.ListObjects(1).DataBodyRange.Value2
End Sub

Thank you for your beautiful answer

 

[Mario_R]

I'm glad it was useful to you.