Combo Boxes built with Control Toolbox on a Worksheet

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
--
Alan

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 GPCombo.Object
.AddItem "Bob"
.AddItem "Lynne"
End With


--

HTH

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


"Alan" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Alan