ExcelBanter - Create Combo Box and Populate

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Create Combo Box and Populate (https://www.excelbanter.com/excel-programming/400147-create-combo-box-populate.html)

Create Combo Box and Populate

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

Create Combo Box and Populate

try add the Userformname

Userformname.objCombo.AddItem "A - AA"

"OssieMac" skrev:

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

Create Combo Box and Populate

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" wrote in message
...
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

Create Combo Box and Populate

Thanks Chip. Very much appreciated.

Regards,

OssieMac

"Chip Pearson" wrote:

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" wrote in message
...
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