O
OssieMac
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
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