ComboBox - syntax for worksheets not user forms

[sallymac]

Good Evening

I am trying to use erik.van.geit's code here to facilitate "cascading" comboboxes. However, my comboboxes are in a worksheet, not a userform and I am unsure which bits of the syntax are incorrect.

Currently I am stuck at
" . . . . .
For I = nr + 1 To N
Controls("ComboBox" & I).Clear
. . . . "
The error message tells me that "Sub or Function not defined."
I think (??) the Controls function relates to User Forms, not activex and worksheets, but I don't know what to replace it with or how to alter the syntax.

I would really appreciate your help.

Thanks
Sally

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim i As Long
    With ActiveSheet
        For i = 1 To 2
            .OLEObjects("ComboBox" & i).Object.Clear
        Next i
    End With
End Sub

 

[sallymac]

Thanks so much Andrew - I'll give it a try tomorrow.