Import data from another sheet based on combobox selection

Question

I am trying to write VBA code that will allow me to select an excel file. It will then give me a combo box that is populated with a list of rates (5Y treasury, 10Y, etc), and based on my selection import the historical rates into my active workbook. I think its safe to say at this point that I have no idea what I'm doing.

I get an error that says

Method 'Range' of object'_Worksheet' failed.

Any help would be greatly appreciated.

Sub Button1_Click()

Dim fd As FileDialog
    Dim strFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Please select a file"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        
        If .Show = True Then
            strFile = .SelectedItems(1)
            Set wb = Workbooks.Open(strFile)
            Set ws = wb.Sheets("output-M")
            
            'Populate combo box with Rates from column E
            For i = 3 To ws.Range("E" & Rows.Count).End(xlUp).Row
                UserForm1.ComboBox1.AddItem ws.Range("E" & i).Value
            Next i
            
            UserForm1.Show
            
            'Import selected rate range to A1:A10 of active sheet
            For i = 1 To 10
                ActiveSheet.Range("A" & i).Value = Application.WorksheetFunction.VLookup(UserForm1.ComboBox1.Value, ws.Range("E:BQ100"), i + 1, False)
                ActiveSheet.Range("A" & i).Value = UserForm1.ComboBox1.Value & " - " & ws.Range("F" & UserForm1.ComboBox1.ListIndex + 2 + i).Value
            Next i
            
            wb.Close False
            
        End If
        
    End With

End Sub

Answer 1

First problem:

VLookup(UserForm1.ComboBox1.Value, ws.Range("E:BQ100"), i + 1, False)

is not a valid range. Based on your other code, you probably want ws.Range("E3:BQ100"), but check your workbook to see where you want the range to start.

Second problem: by default, when the UserForm appears, it will pause the macro until the user closes it. So when the UserForm pops up and you select 5Y treasury, the macro is not running to check your selection. When you close the UserForm, the macro continues, but by that time your selection is lost. You need to set the UserForm Modal = False, then wait for the user to select something.

Instead of:

UserForm1.Show

Use:

    UserForm1.Show False 'Allows user to keep interacting with Excel
    'Wait for user to select something
    Do While UserForm1.ComboBox1.Value = ""
        DoEvents
    Loop