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.Comb oBox.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

Combo Boxes built with Control Toolbox on a Worksheet
 

ALan,

This works for me

Dim GPCombo As Object
Set GPCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.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" wrote in message
...
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.Comb oBox.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