VBA ComboBox/ListBox/etc.

[Flue]

Alright...this is driving me bonkers.

One workbook(main) builds another(slave). I've added a combo box to the other workbook(slave) and I'm trying to populate it from the Main workbook. I keep getting object errors. "Object does not support this property or method."

This Code:

Dim cbox As MSForms.ComboBox
Dim ole As OLEObject

Set ole = ActiveSheet.OLEObjects("ComboBox1")
Set cbox = ole.Object

sets ole and cbox to ''''

If I run the code to populate the combobox1 code from the sheet it resides in things work just fine.

Advice?

Thanks.
Brian

 

[Flue]

Seems I was trying to manipulate an object with out actually defining an object...who knew.

Found some info and managed to get the comboBox working outside of the sheet VB code.

Sub MakeCheckBox()
  Dim WS As Excel.Worksheet
  Dim MyChkOLEObj As Excel.OLEObject
  
  Set WS = ActiveSheet
  
  'create checkbox
  Set ShtComboBx = WS.OLEObjects.Add("Forms.ComboBox.1")
With ShtComboBx
    .Left = WS.Range("L3").Left
    .Top = WS.Range("L3").Top
    .Name = "AnsiCombo"
    .Width = "150"
End With

With WS.OLEObjects("AnsiCombo").Object
    .Clear
    .AddItem "item one"
    .AddItem "item 2"
    .AddItem "3"
End With

End Sub

my main mistake was not actually referencing the Object...just the object Name which wasn't working.