Option Explicit
Dim cbox() As New Class1

Sub ShowDialog()
Dim CBcount As Integer
Dim oleObj As OLEObject ' <= OleObject without the s
Dim cbox1 As MSForms.ComboBox

' Create the ComboBox objects
CBcount = 0
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
CBcount = CBcount + 1
ReDim Preserve cbox(1 To CBcount)
Set cbox1 = oleObj.Object
Set cbox(CBcount).CmboBoxGroup = oleObj.Object
oleObj.Name = cbox1.Name
End If
Next oleObj

End Sub



-- Class

Option Explicit
' use MSforms.ComboBox to be sure
Public WithEvents CmboBoxGroup As MSForms.ComboBox

Private Sub CmboBoxGroup_Click()
Dim cbox1 As MSForms.ComboBox
Dim Target As Range
Set cbox1 = CmboBoxGroup
MsgBox "Hello from " & CmboBoxGroup.Name
Set Target = ActiveSheet. _
OLEObjects(cbox1.Name).TopLeftCell
Target.Offset(1, 0).Select
End Sub



This worked for me

--
Regards,
Tom Ogilvy

steve bell wrote in message
...
Tom,

You're being great...

made these 2 macros and get a Run time error '13', Type
Mismatch at the start of the For loop

Once this code is working, how do I get it to return the
name of the combobox??? And what triggers the macro?

(Working with controls is not my best...)

Your help is much appreciated...

Standard -
Option Explicit
Dim CBox() As New Class1

Sub ShowDialog()
Dim CBcount As Integer
Dim oleObj As OLEObjects

' Create the ComboBox objects
CBcount = 0
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
CBcount = CBcount + 1
ReDim Preserve CBox(1 To CBcount)
Set CBox(CBcount).CmboBoxGroup = oleObj
End If
Next oleObj

End Sub

Class -
Option Explicit

Public WithEvents CmboBoxGroup As ComboBox

Private Sub CmboBoxGroup_Change()
MsgBox "Hello from " & CmboBoxGroup.Name
End Sub

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!