Try something like the following:
Dim objCombo As OLEObject
Sub CreateAndPopulate()
With Sheets("Sheet1")
Set objCombo = .OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=288, Top:=39, _
Width:=193.5, Height:=26.25)
End With
With objCombo
.Name = "MyCombo"
With .Object
.AddItem "A - AA"
.AddItem "B - BB"
.AddItem "C - CC"
.AddItem "D - DD"
.AddItem ("E - EE")
.AddItem ("F - FF")
.ListIndex = 0
End With
End With
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"OssieMac" <(E-Mail Removed)> wrote in message
news:EBC30494-9D9B-4456-9F18-(E-Mail Removed)...
>I am trying to create a combo box and populate it in a macro. If I create
>the
> combo box in one sub and then run a separate sub to populate it then it
> works
> fine.
>
> If I try to create and populate the combo in the same sub then it fails.
> It
> creates and names the combo box but when it tries to populate it returns
> Runtime Error 438. Will very much appreciate any help on what I am missing
> here because I want to be able to put it into a workbook open routine.
>
> My example code as follows:-
>
> The following works if I run the subs separately:-
>
> Dim objCombo As Object
> Sub Create_Combo()
>
> With Sheets("Sheet1")
> .OLEObjects.Add _
> (ClassType:="Forms.ComboBox.1", _
> Link:=False, _
> DisplayAsIcon:=False, _
> Left:=288, Top:=39, _
> Width:=193.5, Height:=26.25) _
> .Select
> End With
> Set objCombo = Selection
> objCombo.Name = "MyCombo"
>
> End Sub
>
> Sub Combo_Populate()
>
> Set objCombo = Sheets("Sheet1").MyCombo
> objCombo.AddItem "A - AA"
> objCombo.AddItem "B - BB"
> objCombo.AddItem "C - CC"
> objCombo.AddItem "D - DD"
> objCombo.AddItem "E - EE"
> objCombo.AddItem "F - FF"
>
> End Sub
>
>
> The following does not work as a single sub:-
>
> Sub CreateAndPopulate()
>
> With Sheets("Sheet1")
> .OLEObjects.Add _
> (ClassType:="Forms.ComboBox.1", _
> Link:=False, _
> DisplayAsIcon:=False, _
> Left:=288, Top:=39, _
> Width:=193.5, Height:=26.25) _
> .Select
> End With
>
> Set objCombo = Selection
> objCombo.Name = "MyCombo"
>
> 'Runtime error 438 occurs with the following
> objCombo.AddItem "A - AA"
> objCombo.AddItem "B - BB"
> objCombo.AddItem "C - CC"
> objCombo.AddItem "D - DD"
> objCombo.AddItem ("E - EE")
> objCombo.AddItem ("F - FF")
>
> End Sub
>
> Regards,
>
> OssieMac
Similar Threads
