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
source to share
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.
source to share