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:
Then, the UserInput form is shown and the code for its initialization looks like:
I get a runtime 424 'Object Required' error. Currently, both the userform and the module are both under the "Normal" project. Any ideas?
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:
Code:
Sub GenerateLetter()'
' GenerateLetter Macro
'
'
UserInput.Show
End SubThen, the UserInput form is shown and the code for its initialization looks like:
Code:
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 = .ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set SCNumberRange = oWB.Sheets("Data").Range("A2:A" & LastRow)
End With
End Sub
