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.
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.