CODEWITHSUNDEEP
vbaexcel
MarkH
MarkH

Reputation: 11

VBA: Calling a sub on another worksheet with multiple arguments

I've been struggling for a while to call a procedure (sub) with arguments that is located on another worksheet. I can call a procedure on another worksheet. I can call a procedure with arguments. But combining the two causes me headache's.

Here's what I have now: 

Private Sub Workbook_Open()
Call Sheet2.FillCombo("Mngt Dashboard", "ComboMonth")
'Sheet2.FillCombo"Operational Dashboard", "ComboMonth2"
End Sub

I tried both syntaxis for calling the procedure but both result in 'subscript out of range (9)'. Here's the procedure being called (located on sheet2):

Sub FillCombo(SheetName As String, ObjName As String)
Dim objCombo As Object
Set objCombo = ActiveWorkbook.Sheets(SheetName).OLEObjects(ObjName).Object
objCombo.Clear
objCombo.AddItem.....
...
End Sub

Is there anyone out there that can show me the light?

Thanks in advance, Mark

Upvotes: 1

Views: 13457

Answers (1)

Logan Spencer
Logan Spencer

Reputation: 11

On Line 2, try dropping the parenthesis:

Sheet2.FillCombo "Mngt Dashboard", "ComboMonth"

Upvotes: 1

Related Questions