I would suggest putting the code to select the workbook in the userform module.

How you call it is up to you, for example you could use the userform's Initialize event or you could have a button on the userform.

For example if you added a button to the form in the workbook Keebellah uploaded you could use this code to allow the user to select a workbook and populate the combobox with its sheet names.
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim wkb As Workbook
Dim fileSelect As FileDialog
Dim selectedFile As String

    selectedFile = Application.GetOpenFilename(, , "Pick a workbook", False)

    If selectedFile = "False" Then
        MsgBox "Cancelled"
        Unload Me
        Exit Sub
    End If

    Set wkb = Workbooks.Open(selectedFile)

    With Me.ComboBox1
        For Each ws In wkb.Worksheets
            .AddItem ws.Name
        Next ws
    End With
    
End Sub

Private Sub ComboBox1_Change()
    If Len(Trim(Me.ComboBox1.Value)) = 0 Then Exit Sub
    MsgBox "Invoke the code to execute the macro on " & Me.ComboBox1.Value & " here!", vbInformation, "THIS IS IT!!!"
    ' do the macro you have to do
    Unload Me
End Sub