View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
george george is offline
external usenet poster
 
Posts: 18
Default CombBox - Object of What Collection?

Tom, Juan,
Thank you for your help. I have tried both methods, but as I am using it with an Excel Worksheet, the OLE method would seem to be the most likely to succeed, though I have tried variations. The below For loop runs through and I watch the value of the oleObj from a Watch Window. After going through some other objects in the application, each of the ComboBoxes is accessed, but none trigger a True response from the TypeOf function. The MessageBox inside the For loop is never reached, though as I mentioned, each of the ComboBoxes is accessed, but just doesn't produce a True to get into the If block. I tried leaving off the MSForms and the access member operator and just using "ComboBox" alone in both places, but it was unsuccessful. I tried declaring oleObj as a variant, but the ComboBoxes are never reset. I tried using for the If Conditional statement:
If TypeName(oleObj) = "ComboBox" Then
But it likewise ran through, but did not trigger a true response.

Dim oleObj As OLEObject, cboItem As MSForms.ComboBox

With Application.Workbooks(1).Worksheets(1)
For Each oleObj In .OLEObjects
If TypeOf oleObj Is MSForms.ComboBox Then
Set cboItem = oleObj
cboItem.ListIndex = -1
MsgBox "Arrived inside If block"
End If
Next oleObj
End With

Apparently, each ComboBox is not being recognized as a ComboBox, though in Design mode, when I selected a ComboBox, the formula bar read: =EMBED("Forms.ComboBox.1","").
Any idea why it is not being recognized?

Thank you much.