Userform Populated with range from another excel file

[jsabo]

Hello,

Trying to automate the generation of templates in Word. I am creating a userform with combo boxes and I would like the combo boxes to be populated from ranges in a separate excel file, which will be closed once the variables are assigned to the ranges (without the user ever seeing the excel file).

First, the below runs from a button on a ribbon:

Sub GenerateLetter()
'
' GenerateLetter Macro
'
'    

UserInput.Show


End Sub

Then, the UserInput form is shown and the code for its initialization looks like:

Private Sub UserForm_Initialize()

    Dim oExcel As Object
    Dim oWB As Object
    Dim SCNumberRange As Range
    Dim LastRow As Long
    
    Set oExcel = CreateObject("Excel.Application")
    Set oWB = oExcel.Workbooks.Open("C:\Users\jsabo\Documents\Letter Generator\Admin\Subcontract_List.xlsx")
    oExcel.Visible = True
    
    With oWB

        LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        SCNumberRange = oWB.Sheets("Data").Range("A2:A" & LastRow)
        
    End With
    
End Sub

I get a runtime 424 'Object Required' error. Currently, both the userform and the module are both under the "Normal" project. Any ideas?

[Bob Phillips]

Set SCNumberRange = oWB.Sheets("Data").Range("A2:A" & LastRow)

[jsabo]

Hm, doesn't seem to have worked. another forum suggested "LastRow = .ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" but that failed to work as well.

[jsabo]

Have been working on this for hours and have refined it to:

Private Sub UserForm_Initialize()

    Dim oExcel As Object
    Dim oWB As Object
    Dim SCNumberRange As Range
    Dim LastRow As Long
    
    Set oExcel = CreateObject("Excel.Application")
    Set oWB = oExcel.Workbooks.Open("C:\Users\jsabo\Documents\Letter Generator\Admin\Subcontract_List.xlsx")
    oExcel.Visible = True
    
    With oWB.Sheets("Data")

        LastRow = .UsedRange.Rows.Count
        Set SCNumberRange = .Range("A2:A" & LastRow)
        
    End With
    
End Sub

But still have an error- this time a "Type Mismatch" error??

[Bob Phillips]

Where is the error occurring?