Combo Boxes built with Control Toolbox on a Worksheet

[Alan]

I would appreciate help on the following. The gist of my problem is that if I
draw a combo from the control toolbox using the mouse everything is fine.
But if I record a macro to do this and then use that macro in the future to
build the combo on user demand I am unable to address the combo from other
VBA code. I enclose the code below.

Option Explicit
Private Sub BuildOneRotaCmd_Click()
'Command Button to trigger filling and or building of the Combo box GPCombo
'I have commented out the lines which cause failure.
'So the line below oleobjects.add is the one cretaing the basic problem

'ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=124.8, Top:=91.2, Width:=83.4, Height:= _
17.4).Select
'Selection.Name = "GPCombo"
Range("a1").Select
ActiveWorkbook.Save
FillTheCombo
End Sub
Private Sub FillTheCombo()
Dim counter As Integer
Sheets("welcome").Select
With GPCombo 'Fails here if Combo box built by code above with message
Variable undefined
'With ActiveSheet.OLEObjects("GPCombo") 'If I replace the above "With" with _
this line it works but fails on the
Additem _
message "Object doesn't support this
property or method

For counter = 10 To 25
.AddItem Sheets("Rota").Cells(counter, 10)
Next counter
End With

End Sub
Private Sub GPCombo_Click()
'GPCode is a Public varaible
GPcode = GPCombo.Value
MsgBox GPcode

If Sheets("sheet2").Range("a3") = GPcode Then
MsgBox "Thats good it matches the selection"
End If
End Sub

 

[Bob Phillips]

ALan,

This works for me

Dim GPCombo As Object
Set GPCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
_
Left:=124.8, Top:=91.2, Width:=83.4, Height:=17.4)
GPCombo.Name = "GPCombo"
With GPCombbject
.AddItem "Bob"
.AddItem "Lynne"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)