Populate combobox with sheets name

[jayymehta]

Hi, I have a Sub which selects an Excel file by dialog box. On selecting the file, a TextBox (Besides the Browse button in Userform) displays the full path of the selected file. Now There's also a Combobox below that textbox in which I want to populate all the Sheet names which are there in the selected file.
For ex, There is an excel file "Test.xlsm" in which are 3 sheets (Sheet1, Sheet2, Sheet3). So when "Test.xslm" is selected, the combobox should populate with "Sheet1", "Sheet2" and "Sheet3" names inside it. I tried making the code for above, but don't know how to deal with populating combobox with sheet names.

Private Sub CommandButton1_Click()  Dim fName As String
  Dim subfiles As Worksheets
  
  fName = Application.GetOpenFilename("Excel Files(*.xlsm),*.xlsm", , "Please select one **** excel file", , False)
  
  If fName = "" Then
    MsgBox "no file is selected.", vbExclamation, "Sorry!"
     Exit Sub
  Else
    TextBox1.Value = fName
  End If
  
  Set subfiles = fNames.Worksheet(i)
  
  For Each subfiles In fName
    .ComboBox1.AddItem = i
  Next i
    
End Sub

Kindly help me out of this.

 

[My Aswer Is This]

To load the sheet names into a ComboBox you would need a script like this.
But I do believe the workbook would have to be open and not just selected.

Private Sub CommandButton3_Click()
Dim i As Long
    For i = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(i).Name
    Next
End Sub