Listbox Rowsource refresh from filtered table

[Earlyfreak]

I have a listbox on a Useform in VBA and I have the listbox load as RowSource from tbl_Exercises
It loads all exercises and muscle groups in listbox

I have buttons on Userform to select a muscle group

Example

Private Sub B_NewYork_Click()
Sheets("Data").ListObjects("tbl_Data").Range.AutoFilter Field:=1
Sheets("Data").ListObjects("tbl_Data").Range.AutoFilter Field:=1, _
Criteria1:="Biceps"
End Sub

This filters the table, however it does not update the listbox on form

I tried using this code to maybe just show visible filtered table but did not work

Private Sub UserForm_Initialize()
Listbox1.RowSource = Sheets("Data").Range("D7").CurrentRegion.Select
End Sub

I looking to filet listbox based on form button and only show that muscle group

Any direct on how to update listbox to only show filtered table RowSource?

 

[Earlyfreak]

Came up with a solution by watching a youtube video by Paul Kelly Excel Macro Mastery, Thanks excellent content.

I created an advanced filter on the table to a different output range then added to listbox

' Add the data to the ListBox using RowSource
Private Sub AddDataToListbox()
 ' Get the data range
 Dim rg As Range
 Set rg = GetRange
' Link the data to the ListBox
 With ListboxExercises
 .RowSource = rg.Address(External:=True)
 .ColumnCount = rg.Columns.Count
 .ColumnWidths = "80;85;85;150;150;100"
.ColumnHeads = True
  .ListIndex = 0
  End With
End Sub

Public Function GetRange() As Range
   Set GetRange = shExercises.Range("I1").CurrentRegion
  Set GetRange = GetRange.Offset(1).Resize(GetRange.Rows.Count - 1)
End Function

Private Sub UserForm_Initialize()
AddDataToListbox
End Sub