So I have a dropdown menu in column A and B. With the next code, I change the content based on the selected value. Both codes work and don't give an error.
However... If I add or delete a row, Excel crashes. I found out that the second part of the code causes this. (so the code for column A)
Both codes active: Crash
Only code for column A active: Crash
Only code for column B active: fine.
Why? The only problem I can think of is that when you right-click to add a row, it automatically selects the entire row, which activates the first cell (in column A) which might mess with the code.
How can I fix this?
However... If I add or delete a row, Excel crashes. I found out that the second part of the code causes this. (so the code for column A)
Both codes active: Crash
Only code for column A active: Crash
Only code for column B active: fine.
Why? The only problem I can think of is that when you right-click to add a row, it automatically selects the entire row, which activates the first cell (in column A) which might mess with the code.
How can I fix this?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo fout
'om de inhoud van de cel aan te passen na een keuze door dropdown in de kolom TAG
selectedNa = Target.Value
If Target.Column = 2 Then
selectedNum = Application.VLookup(selectedNa, Worksheets("Dropdown").Range("LookUp"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
'---------------------------------------------------------
'om de inhoud van de cel aan te passen na een keuze door dropdown in de kolom Systeem
selectedNa2 = Target.Value
If Target.Column = 1 Then
selectedNum2 = Application.VLookup(selectedNa2, Worksheets("Dropdown").Range("LookUp2"), 2, False)
If Not IsError(selectedNum2) Then
Target.Value = selectedNum2
End If
End If
'----------------------------------------------------------
Exit sub
fout:
Target.Value = ""
Exit Sub
End Sub