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

Which line does it stop on when you click debug? – braX Apr 21 '23 at 22:34 — braX, Apr 21 '23 at 22:34

score 0 · Accepted Answer · answered Apr 22 '23 at 01:56

0

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

answered Apr 22 '23 at 01:56

kevin

1,357
1
4
10

Thanks Kevin. The first problem was a typo. I was trying both `E:BQ` and `E3:BQ100`. The solution to the second problem cleared up the error I was getting but I now have a 3rd problem in that the code runs, I select a rate, but nothing happens. I also realize that the vlookup code did not loop enough times and I changed that to 64 times to reflect looping from columns F through BQ. But nothing. Any thoughts? – jmt78 Apr 22 '23 at 17:02
Never mind. I realize my mistake. I can't use ActiveSheet to reference my destination worksheet as the ActiveSheet is no longer the destination worksheet at the time the code runs. It works perfectly once I refer to the exact workbook and worksheet. Thanks for the help! – jmt78 Apr 22 '23 at 17:19

Import data from another sheet based on combobox selection

1 Answers1