Utilizing a while loop to create combo boxes within Excel Spreadsheet

Question

I need combo boxes to be placed in specific positions in relation to the cells on this Worksheet. I was attempting to utilize a while loop to change a variables value and utilize that variable to dynamic change a cell. However, my code doesn't seem to be working. The loop will run through once, but will give me a "object does not support this property or method" error on the successive loop.

Dim t As Integer

Dim wotype as object

t = 1

Do While t < 43

    wotype = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(4, t).Left, Top:=Cells(3, t).Top, Width:=25.29, Height:=Rows(3).RowHeight)

    t = t + 3

Loop

Answer 1

When assigning an object to a variable, you need to use the Set keyword...

set wotype = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(4, t).Left, Top:=Cells(3, t).Top, Width:=25.29, Height:=Rows(3).RowHeight)

Although, in this case, since you're not subsequently referring to your variable, your code can be re-written as follows...

Dim t As Integer

t = 1

Do While t < 43

    ActiveSheet.Shapes.AddFormControl xlDropDown, Left:=Cells(4, t).Left, Top:=Cells(3, t).Top, Width:=25.29, Height:=Rows(3).RowHeight

    t = t + 3

Loop