VBA: dynamically created combobox embedded to a cell

Viewed 32

What I am trying to do is to create a combobox embedded to a cell. Code below finds first available row, in this case would be 12 and offsets to column :

Dim ctop#, cleft#, cht#, cwdth#
Dim ComboBox As OLEObject
                
ctop = Sheets("Sheet1").Range("O35").End(xlUp).Offset(0, 5).Top
cleft = Sheets("Sheet1").Range("O35").End(xlUp).Offset(0, 5).Left
cht = Sheets("Sheet1").Range("O35").End(xlUp).Offset(0, 5).Height
cwdth = Sheets("Sheet1").Range("O35").End(xlUp).Offset(0, 5).Width
                    
With ws_new
Set ComboBox = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=cleft, Top:=ctop, Width:=cwdth, Height:=cht)
End With
                    
ComboBox.Placement = xlMoveAndSize

What this code actually does, is it creates a combobox somewhere between Q12 and R12, and it is not embedded to the cell. Combobox resizes with the column and row, but it is not attached to a single cell as wanted. What am I doing wrong here?

edit:

Here is a screenshot what the code does for me

0 Answers
Related