Compiling error when initializing excel with dependant ActiveX Comboboxes

Good afternoon! I'm an extreme newbie when it comes to coding, and i've been dabbling a little with Excel VBA for a project i want to make for my boss. It's something quite simple, but it took me a while to understand everything that's going on, but i got it to work.

Summary: There's table with three levels of sub-items (So 1 / 1.1 / 1.1.1) and i've managed to make three comboboxes where 3 is dependant on what you pick on 2, and 2 is dependant on what you pick on 1; So if i pick 1 on CB1, it opens up the sub-items for 1 (1.1) and if i pick 1.1 on CB2, it opens up 1.1.1 on three They need to be ActiveX comboboxes becase my map is big, and when i zoom out i need it to be big, which i cant do with data validation dependant lists.

The issue i'm facing is, every other time i save and close the workbook, when it launches, it shows a compile error in CB1 sub. What i think is happening is when i open the workbook, for some reason it only understands that CB1 exists, but no CB2/CB3, so when it sees a reference to CB2/CB3 on the CB1 sub, it shows a compile error because CB/CB3 technically dont exist yet? Then as soon as i close the VBA editor, it understands those two exist, and i dont get an error anymore, until i close/launch the workbook 2 times, and it repeats.

This is the code i have:

Private Sub ComboBox2_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Listas")

Dim i As Integer

Me.ComboBox3.Clear

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "N3" Then
        If sh.Range("C" & i).Value = Me.ComboBox2.Value Then
        Me.ComboBox3.AddItem sh.Range("B" & i)
        End If
    End If
Next i

End Sub

Private Sub ComboBox1_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Listas")

Dim i As Integer

Me.ComboBox2.Clear
Me.ComboBox3.Clear

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "N2" Then
        If sh.Range("C" & i).Value = Me.ComboBox1.Value Then
        Me.ComboBox2.AddItem sh.Range("B" & i)
        End If
    End If
Next i

End Sub



Private Sub Worksheet_Activate()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Listas")

Dim i As Integer

Me.ComboBox1.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "N1" Then
        Me.ComboBox1.AddItem sh.Range("B" & i)
    End If
Next i


End Sub

The compile error is in this line every other time i launch the workbook:

Private Sub ComboBox1_Change()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Listas")
    
    Dim i As Integer
    
    Me.ComboBox2.Clear
    Me.ComboBox3.Clear

I appreciate any inputs that could help me with this project. Thank you very much!



Comments

Popular posts from this blog

Spring Elasticsearch Operations

Object oriented programming concepts (OOPs)

Spring Boot and Vaadin : Filtering rows in Vaadin Grid