2022-04-21

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!



No comments:

Post a Comment