Method or data error not found

I have a list box with a select list and by changing the selection it creates another combo form with a different list of options at a different position. Problems:

  • I cannot add items to the created combobox
  • I cannot clear any created combo boxes created when the selection changes

I tried the following code, but it gives the "Method or data 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

      

+3


source to share


1 answer


You need to make sure these ComboBoxes are created, otherwise you will get this compilation error.

Without a clear purpose for these ComboBoxes (MaintLevel, Workload, Breeding), the only reason I think you want them to be the same size and position is because each one only refers to one of the main ComboxBox variants with named ComboBox1.

However, you will need to create different Subs for each and one of the ComboBoxes - a terrible decision for future code maintenance.

What I would like to create:

  • When opening a workbook, check if there are 2 ComboBoxes in the sheet1 text and create them if needed
  • After Sheet1 activates, set initial options in the combo box: Level1Choice
  • When changing Level1Choice socket options list Level2Choice be changed in accordance with the index Level1Choice
  • When Level2Choice changes , you can call different Subs that do different things in a 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 get rid of the thread.

+2


source







All Articles