How to Place Combo box in range of cells

[JFKJr]

Hello everyone!

The following is the Access VBA code which opens an excel file and adds a combo box to it.

But I would like to place the combo box in particular range of cells say (A1:A20), can anyone suggest me how to do this?

Thanks in advance.

Function Create_ComboBox()
Dim XL As Excel.Application, WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set XL = New Excel.Application
XL.Visible = True
XL.Interactive = True
Set WB = XL.Workbooks.Open("C:\Book1.xls", , False)
Set WS = WB.Worksheets("Example")
WS.Activate

XL.CommandBars("Control Toolbox").Visible = False
WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=162, Top:=32.25, Width:=110.25, Height:= _
    33.75).Select
End Function

[janders468]

I haven't used controls on a worksheet in quite some time but if memory serves correct there is no association between the control and the cells (say like a background color would be). If you want it to cover the range you mentioned you will have to do so by adjusting the size of the combobox based on the default size of cells when you create a workbook. It will probably just take trial and error to figure out the correct size.