Fill combobox with worksheet from another workbook

[Vagelisr]

Hi to all

I want to fill a combobox with the names of a close workbook

I try

Private Sub UserForm_Activate()
    Dim i As Long
    Dim ans As Long
   
    ans = Workbooks(FileToOpen).Sheets.Count
    ComboBox1.Clear
    For i = 1 To ans
        ComboBox1.AddItem Workbooks(FileToOpen).Sheets(i).Name
    Next
End Sub

but i receive
run-time error 9
Subscript out of range

Any help???

 

[My Aswer Is This]

To retrieve or modify anything on a workbook the workbook must be open.
And this cannot be a proper workbook name:
Workbooks (FileToOpen)
All Workbooks would have an extension like .xlsm

 

[Vagelisr]

The FileToOpen is a string where includes the excel file i want to read his worksheets

Public FileToOpen As String

With this code

Sub Get_Sheet_From_File()
    FileToOpen = Application.GetOpenFilename(Title:="Select File for Open", FileFilter:="Excel Files *.xls* (*.xls*),")
    If Len(FileToOpen) = 0 Then
        MsgBox "Please give file name"
        Exit Sub
    ElseIf FileToOpen = "False" Then
        Exit Sub
    Else
        UserForm1.Show
    End If
End Sub

Even if i open the workbook filetoopen the error remain