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
Post a Comment