New object on spreadsheet
 

I am creating a combobox using vba. (with OLEObjects.Add)
Since there will be several, I want to name them myself and have code ready
to support them. I can set the name property as I wish, but it doesn't take
effect until after the procedure in whice I created the object is completed.
I would also like to add the listitems and position the boxes relative to
one another. I can do this in vba as well, but not in the same procedure in
which they are created. This has meant having two command buttons: one to
create, and one to setup.
Is there a method I can call in the create code that will update the
controls collection so that I can setup in the same procedure? Something
like Refresh, as used on other controls like ADO controls, etc.
I can't find one. Help!
Thanks,
--
Gary

New object on spreadsheet
 

I don't understand what you mean, this works for me

'-----------------------------------------------------------------
Sub CreateCombobox()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

oOLE.Name = "myBox"
MsgBox oOLE.Name
oOLE.ListFillRange = "A1:A10"

End Sub


--
HTH

Bob Phillips

"Gary Labowitz" wrote in message
...
I am creating a combobox using vba. (with OLEObjects.Add)
Since there will be several, I want to name them myself and have code
ready
to support them. I can set the name property as I wish, but it doesn't
take
effect until after the procedure in whice I created the object is
completed.
I would also like to add the listitems and position the boxes relative to
one another. I can do this in vba as well, but not in the same procedure
in
which they are created. This has meant having two command buttons: one to
create, and one to setup.
Is there a method I can call in the create code that will update the
controls collection so that I can setup in the same procedure? Something
like Refresh, as used on other controls like ADO controls, etc.
I can't find one. Help!
Thanks,
--
Gary

New object on spreadsheet
 

"Bob Phillips" wrote in message
...
I don't understand what you mean, this works for me

'-----------------------------------------------------------------
Sub CreateCombobox()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

oOLE.Name = "myBox"
MsgBox oOLE.Name
oOLE.ListFillRange = "A1:A10"

It's here that we differ. What I was trying is oOle.AddItem("Hello")
etc. This seems not to work. I had tried using a different name (using Dim
and Set, but couldn't seem to get that to work).
Thank you.
--
Gary