By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,220 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,220 IT Pros & Developers. It's quick & easy.

How to Place Combo box in range of cells

100+
P: 126
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.

Expand|Select|Wrap|Line Numbers
  1. Function Create_ComboBox()
  2. Dim XL As Excel.Application, WB As Excel.Workbook
  3. Dim WS As Excel.Worksheet
  4.  
  5. Set XL = New Excel.Application
  6. XL.Visible = True
  7. XL.Interactive = True
  8. Set WB = XL.Workbooks.Open("C:\Book1.xls", , False)
  9. Set WS = WB.Worksheets("Example")
  10. WS.Activate
  11.  
  12. XL.CommandBars("Control Toolbox").Visible = False
  13. WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
  14.     DisplayAsIcon:=False, Left:=162, Top:=32.25, Width:=110.25, Height:= _
  15.     33.75).Select
  16. End Function
Aug 13 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 112
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.
Aug 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.