ich hatte noch den Teil vergessen, wenn B3 geleert wird und die "" & "" entfernt:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngS As Long
Dim strS As String
Dim strV(1 To 4, 1 To 3) As String
If Target.Cells(1).Address = "$B$3" Then
strS = LCase(Left(Target.Cells(1).Value, 4))
lngS = ((strS = "deut") * -1) + ((strS = "fran") * -2) + ((strS = "ital") * -3)
If lngS Then
strV(1, 1) = "Präsenzkurs"
strV(2, 1) = "Onlinekurs"
strV(3, 1) = "Kurs ohne Theorieanteil"
strV(4, 1) = "Kurs mit Theorieanteil"
strV(1, 2) = "Cours de présence"
strV(2, 2) = "Cours en ligne"
strV(3, 2) = "Cours sans partie théorique"
strV(4, 2) = "Cours avec partie théorique"
strV(1, 3) = "Corso in aula"
strV(2, 3) = "Corso online"
strV(3, 3) = "Corso senza teoria"
strV(4, 3) = "Corso con componente teorica"
Select Case Range("B4").Value
Case strV(1, 1), strV(1, 2), strV(1, 3)
Range("B4").Value = strV(1, lngS)
Case strV(2, 1), strV(2, 2), strV(2, 3)
Range("B4").Value = strV(2, lngS)
End Select
If Not Intersect(Range("B4"), Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
Range("B4").Validation.Modify xlValidateList, , , strV(1, lngS) & ", " & strV(2, lngS)
Else
Range("B4").Validation.Add xlValidateList, , , strV(1, lngS) & ", " & strV(2, lngS)
End If
Select Case Range("B5").Value
Case strV(3, 1), strV(3, 2), strV(3, 3)
Range("B5").Value = strV(3, lngS)
Case strV(4, 1), strV(4, 2), strV(4, 3)
Range("B5").Value = strV(4, lngS)
End Select
If Not Intersect(Range("B5"), Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
Range("B5").Validation.Modify xlValidateList, , , strV(3, lngS) & ", " & strV(4, lngS)
Else
Range("B5").Validation.Add xlValidateList, , , strV(3, lngS) & ", " & strV(4, lngS)
End If
Else
Range("B4:B5") = ""
If Not Intersect(Range("B4"), Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
Range("B4").Validation.Delete
End If
If Not Intersect(Range("B5"), Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
Range("B5").Validation.Delete
End If
End If
End If
End Sub