Using VBA to set column widths of several combo boxes in Excel

[SJohnE]

I have an Excel spread sheet which serves as a template for users to request stock codes be created. There are several combo boxes tied to some ranges which provide them the ability to select what they want in that cell.

I need to write a utility that will allow me to modify the properties for the combo boxes without going to each one specifically.

How can I iterate through them say all that are in column I with a name between combobox31 thru combobox 45 and set the width for the two columns that are displayed to 30pts; 130pts.

I was able to set the linked cell property but for some reason I can't find the magic reference to the column width to set it. the coded used for the linked cell is as follows...

Public Sub UpdateLinkCtrls()
Dim cntr#, S#, E#, CntrStart#
Dim LC$

S# = InputBox("Enter First combobox number of range")
E# = InputBox("Enter Last combobox number of range")
LC$ = InputBox("Enter Column letter for linked field")
Cntr# = InputBox("Enter counter start for linked field")

For n = S# To E#
Worksheets(1).OLEObjects("Combobox" & CStr).LinkedCell = LC$ & cntr
cntr = cntr + 1
Next
End Sub

this works well and fast.

Any suggestions for doing similar but setting column width?



S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

Hi,

Ranges do not have properties that identify objects.

You must loop thru your OLEObjects to find an OLEObject that has a TopLeftCell property that is in the range of interest...

dim ole as OLEObject

for each ole in activesheet.OLEObjects 
   if not intersect(ole.topleftcell, activesheet.columns("I:I")) is nothing then
      'Houston, we have a hit!  Now do yer thing using the ole object
      ole.columnwidths = "30pts; 130pts"
      'or something similar
   end if
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

Skip,
Perhaps I should have been more clear, the error I am getting says Run-time error 438 Object doesn't support this property or method. I get the same error with your code.

I even tried this for a test to see if I could get it to work...

* ActiveSheet.OLEObjects("combobox31").ColumnWidths
= "30;130"

Above code Fails

* ActiveSheet.ComboBox31.ColumnWidths = "30;130"

Above code works but can't find a way to use variable in place of ComboBox31

I even tried

Dim n#
Dim myctrl as object
dim s#, e#

S# = InputBox("Enter First combobox number of range")
E# = InputBox("Enter Last combobox number of range")

for n = s# to e#
set myctrl = "Combobox" & cstr
ActiveSheet.myctrl.ColumnWidths = "30;130"
next n

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

Is this a...

1) Control Toolbox Control

2) Forms Control

3) Data Validation Control

???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

This is a Combobox from the controls toolbox

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

How did you load your combobox?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

Used the property and specified the range to read from. the combo boxes work for loading the data into the cell, I am just trying to automate setting the column size properties. Wanted to write a utility so when I have a lot of controls like this I can automate it. I actually ended up doing it by hand but it was very time consuming as there were three hundred to do. This is for a template that provides use ability to request upto 100 stock codes be generated at a time. So each row has a drop down to populate some fields.

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

Used the property and specified the range to read from.

'The property'??? WHICH property?

'The range'??? WHAT range and how did you specify?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

The comboboxes are added to the spread sheet from the Control toobox toobar. I right click on the combobox and select Properties from the pop up menu. Properties dialog opens and in the property titled "Listfillrange" Identified the range name of in the spreadsheet that has the fill data for the combobox, this is not the issue though, that works perfectly. The issue is that I am trying to set the columnwidths property of the combobox using VBA.

This isn't something that the users of the spread sheet will see, its a utility to allow me to add additional combo boxes and not have to go to each combobox - Rightclick - select properties and then set the properties. I want to answer some questions from prompts and use VBA to set the properties for all the comboboxes with the code names of combobox1 - combobox100 for example.

I was able to do this for the "LinkCell" property but for some reason the syntax is different for columnwidths.

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

I am trying to understand what COLUMNS you are referring to?

Please post an example of the ListFillRange and the DATA in that range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

Not sure why it would matter, but in column AB & AC is a range called Ccd
In column AB is a list of the codes:
AE00
AE01
AE02
...

In column AC is a list of their values or descriptions
Small Hand tools
Hand tools over 1000.00 value
Hand Held Power Tools

the combo boxes populate just fine that is not a problem, the combo box property for Listfillrange is Ccd.
I want to size the displayed columns in the combo box so that the AE00 does not get cut of when you click the drop down to AE0 for example.

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.