473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to Place Combo box in range of cells

126 100+
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
1 1473
janders468
112 Expert 100+
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

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

Similar topics

2
by: Michael Jordan | last post by:
I'm hoping that someone here can give me some insight into a problem I'm running into with Python, pywin32 and Excel. All-in-all using Python and pywin32 is great but I've run into a strange...
3
by: zxo102 | last post by:
Hi there, I need your help for python <--> excel. I want to paste selected cells (range) to different location on the same sheet in Excel through python. I have tried it for a while but could not...
2
by: kscdavefl | last post by:
When I run the following code: private void applicationPermissionGrid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { if (e.Item.ItemType ==...
1
by: Red | last post by:
Ok, yada yada yada, I know this is the Access group, however, I need help in Excel :D Here's the thing, I'm exporting data to excel (From Access), adding code from a string, and executing...
4
by: dilau | last post by:
I have a problem. The error in subject appear when i run the macro Can u tell me why Case "CENTRALIZATOR SURVEY" Dim SHT As Object Set...
0
by: iain654 | last post by:
I have finally worked out how to automatically send a range of cells in the body of an email using Outlook but it is very clumsy and I have to build up the email using the limit of line...
6
by: JFKJr | last post by:
Hello everyone, the following is the Access VBA code which opens an excel spreadsheet and creates combo boxes dynamically. And whenever a user selects a value in a combo box, I am trying to pass...
0
by: drfish | last post by:
Hi, I'm a complete novice when it comes to VB so need some help. I would like a macro that performs linear regression for different cell ranges each time it is run, depending on the number of...
1
by: Bonzs | last post by:
I have troule with this macro... geting the used rsnge... Public strName As String, ws As Worksheet Sub Test() Workbooks.Open Filename:= _ "C:\Documents and Settings\User\Desktop\IT...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.