"Method or Data Member not Found" error

Question

I have a combobox with a list of choices and by changing the choice it creates another combobox with another list of choices in another position. The problems are:

1. I can’t add items to the created combobox
2. I can’t clear any other created comboboxes when I change the choice

I tried the following code but it gives “Method or data member not found” error.

Private Sub ComboBox1_Change()
Dim index As Integer
index = ComboBox1.ListIndex
ActiveSheet. MaintLevel.Clear
ActiveSheet. WorkLoad.Clear
ActiveSheet. Breeding.Clear

Select Case index
Case Is = 0
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=324.75, Top:=40.5, Width:=108,  _
        Height:=17.25).Name = "MaintLevel"
With Sheet1.MaintLevel
.AddItem "Low"
.AddItem "Average"
.AddItem "High"
End With
Case Is = 1
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=324.75, Top:=40.5, Width:=108, _
        Height:=17.25).Name = "WorkLoad"
With Sheet1. WorkLoad
.AddItem "Light"
.AddItem "Medium"
.AddItem "Heavy"
End With

Case Is = 2
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=324.75, Top:=40.5, Width:=108, _
        Height:=17.25).Name = "Breeding"
With Sheet1. WorkLoad
.AddItem "No"
.AddItem "Yes"
End With
End Select
End Sub

Answer 1

You must ensure those ComboBoxes are created otherwise you get that Compile Error.

Without a clear purpose of those ComboBoxes (MaintLevel,Workload,Breeding), the only reason I think you want them to be same size and position is that each of it relates to only one choice of the master ComboxBox named ComboBox1.

However, you will then have to create Different Subs for each and single one of the ComboBoxes - horrible decision for future code maintaining.

What I would design:

1. Upon workbook open, check if there are 2 ComboBoxes in say Sheet1 and create them if required
2. Upon Sheet1 is Activates, set initial options to combo box say Level1Choice
3. Upon changes to Level1Choice, the combo box options of Level2Choice will be changed according to the index of the Level1Choice
4. Upon changes to Level2Choice, you can then call different Subs which do different things in standard module.

---

Code in ThisWorkbook module

Option Explicit

Private Sub Workbook_Open()
    CheckComboBoxesInSheet1
End Sub

Private Sub CheckComboBoxesInSheet1()
    On Error Resume Next
    With ThisWorkbook.Sheets("Sheet1")
        If .OLEObjects("Level1Choice") Is Nothing Then
            .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, Left:=324.75, Top:=10.5, Width:=108, _
                Height:=17.25).Name = "Level1Choice"
        End If
        If .OLEObjects("Level2Choice") Is Nothing Then
            .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, Left:=324.75, Top:=40.5, Width:=108, _
                Height:=17.25).Name = "Level2Choice"
        End If
        .Activate
    End With
End Sub

---

Code in Sheet1 Module:

Option Explicit

Private Sub Worksheet_Activate()
    InitializeLevel1
End Sub

Private Sub InitializeLevel1()
    Dim i As Long
    With Me.Level1Choice
        .Clear
        For i = 1 To 5
            .AddItem "Choice " & i
        Next
        .Value = "Select an option"
    End With
End Sub

Private Sub Level1Choice_Change()
    Dim bShow As Boolean
    bShow = True
    With Me.Level2Choice
        .Clear
        Select Case Me.Level1Choice.ListIndex
            Case 0 '=== [ MaintLevel ]===
                .AddItem "Low"
                .AddItem "Average"
                .AddItem "High"
            Case 1 '=== [ WorkLoad ]===
                .AddItem "Light"
                .AddItem "Medium"
                .AddItem "Heavy"
            Case 2 '=== [ Breeding ]===
                .AddItem "No"
                .AddItem "Yes"
            Case Else
                bShow = False
        End Select
        .Visible = bShow
        If bShow Then .Activate
    End With
End Sub

Private Sub Level2Choice_Change()
    Dim sLevel1Value As String, sLevel2Value As String
    sLevel1Value = Me.Level1Choice.Value
    sLevel2Value = Me.Level2Choice.Value
    Debug.Print sLevel1Value, sLevel2Value
End Sub

Hopefully I am making myself clear of the flow.