Prev: VBA Password Removal??
Next: vlookup worksheetfunction
From: J Streger on 15 Feb 2005 11:29
I am using Excel 2000.

I created a sheet that automatically creates and adds comboboxes into the
row where new data was entered. I am using the code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=sLeftVal, Top:=sTopVal, Width:=sWidth,
Height:= _
sHeight).Select

this code adds a VBA combobox, which is what I want as I want to create
multi-columned comboboxes. The problem comes up that in order to access the
columns method of the VBA combobox, I need to call the combobox by name
(application.combobox1). If I use the shapes("Combobox1") path, Excel
considers the combobox an excel combobox, and has no columns property. So i
want to find out if there is a way to use varient to call a specific
combobox, so that it allows the use of the columns property. A friend
suggested creating an array of comboboxes (rcombobox() as combobox) but now I
can't find a method in excel that will let me load that array with VBA
comboboxes. Any help would be most appriciated.
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

From: Tom Ogilvy on 15 Feb 2005 11:44

set OleObj =ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=sLeftVal, _
Top:=sTopVal, _
Width:=sWidth, _
Height:= _
sHeight
OleObj.Name = "ComboboxAA"

now
Activesheet.OleObjects("ComboboxAA") is an OleObject
Activesheet.OleObjects("ComboboxAA").Object is a VBA combobox

--
Regards,
Tom Ogilvy


"J Streger" <JStreger(a)discussions.microsoft.com> wrote in message
news:1E3CF09B-B68F-4714-995D-B7B0703F8814(a)microsoft.com...
> I am using Excel 2000.
>
> I created a sheet that automatically creates and adds comboboxes into the
> row where new data was entered. I am using the code:
>
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
Link:=False, _
> DisplayAsIcon:=False, Left:=sLeftVal, Top:=sTopVal, Width:=sWidth,
> Height:= _
> sHeight).Select
>
> this code adds a VBA combobox, which is what I want as I want to create
> multi-columned comboboxes. The problem comes up that in order to access
the
> columns method of the VBA combobox, I need to call the combobox by name
> (application.combobox1). If I use the shapes("Combobox1") path, Excel
> considers the combobox an excel combobox, and has no columns property. So
i
> want to find out if there is a way to use varient to call a specific
> combobox, so that it allows the use of the columns property. A friend
> suggested creating an array of comboboxes (rcombobox() as combobox) but
now I
> can't find a method in excel that will let me load that array with VBA
> comboboxes. Any help would be most appriciated.
> --
> *********************
> J Streger
> MS Office Master 2000 ed.
> MS Project White Belt 2003
>


From: J Streger on 15 Feb 2005 14:15
Thank you very much. That is exactly what I needed!

"Tom Ogilvy" wrote:

>
> set OleObj =ActiveSheet.OLEObjects.Add( _
> ClassType:="Forms.ComboBox.1", _
> Link:=False, _
> DisplayAsIcon:=False, _
> Left:=sLeftVal, _
> Top:=sTopVal, _
> Width:=sWidth, _
> Height:= _
> sHeight
> OleObj.Name = "ComboboxAA"
>
> now
> Activesheet.OleObjects("ComboboxAA") is an OleObject
> Activesheet.OleObjects("ComboboxAA").Object is a VBA combobox
>
> --
> Regards,
> Tom Ogilvy
>
>
> "J Streger" <JStreger(a)discussions.microsoft.com> wrote in message
> news:1E3CF09B-B68F-4714-995D-B7B0703F8814(a)microsoft.com...
> > I am using Excel 2000.
> >
> > I created a sheet that automatically creates and adds comboboxes into the
> > row where new data was entered. I am using the code:
> >
> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
> Link:=False, _
> > DisplayAsIcon:=False, Left:=sLeftVal, Top:=sTopVal, Width:=sWidth,
> > Height:= _
> > sHeight).Select
> >
> > this code adds a VBA combobox, which is what I want as I want to create
> > multi-columned comboboxes. The problem comes up that in order to access
> the
> > columns method of the VBA combobox, I need to call the combobox by name
> > (application.combobox1). If I use the shapes("Combobox1") path, Excel
> > considers the combobox an excel combobox, and has no columns property. So
> i
> > want to find out if there is a way to use varient to call a specific
> > combobox, so that it allows the use of the columns property. A friend
> > suggested creating an array of comboboxes (rcombobox() as combobox) but
> now I
> > can't find a method in excel that will let me load that array with VBA
> > comboboxes. Any help would be most appriciated.
> > --
> > *********************
> > J Streger
> > MS Office Master 2000 ed.
> > MS Project White Belt 2003
> >
>
>
>
 | 
Pages: 1
Prev: VBA Password Removal??
Next: vlookup worksheetfunction