Datenüberprüfung-Liste doppelte Einträge verhinder
23.07.2015 13:39:42
Laserpeak
Hallo Leute
hier mal mein Code, könnte sich den bitte mal wer durchsehen, vielleicht findet jemand den Fehler
Private Sub Workbook_Open()
Call ComboBox1
Call ComboBox2
Call ComboBox3
Call ComboBox4
Call ComboBox5
End Sub
Sub ComboBox1()
Dim Eleere As Long
Dim Lvolle As Long
Eleere = Cells(Rows.Count, 1).End(xlUp).Row + 1
Lvolle = Eleere - 1
MsgBox Lvolle
Range("$B$&Eleere:$B$65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="$B$2:$B$&Lvolle"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Sub ComboBox2()
Dim Eleere As Long
Dim Lvolle As Long
Eleere = Cells(Rows.Count, 1).End(xlUp).Row + 1
Lvolle = Eleere - 1
MsgBox Lvolle
Range("$C$&Eleere:$C$65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="$C$2:$C$&Lvolle"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Sub ComboBox3()
Dim Eleere As Long
Dim Lvolle As Long
Eleere = Cells(Rows.Count, 1).End(xlUp).Row + 1
Lvolle = Eleere - 1
MsgBox Lvolle
Range("$D$&Eleere:$D$65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="$D$2:$D$&Lvolle"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Sub ComboBox4()
Dim Eleere As Long
Dim Lvolle As Long
Eleere = Cells(Rows.Count, 1).End(xlUp).Row + 1
Lvolle = Eleere - 1
MsgBox Lvolle
Range("$E$&Eleere:$E$65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="$E$2:$E$&Lvolle"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Sub ComboBox5()
Dim Eleere As Long
Dim Lvolle As Long
Eleere = Cells(Rows.Count, 1).End(xlUp).Row + 1
Lvolle = Eleere - 1
MsgBox Lvolle
Range("$F$&Eleere:$F$65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="$F$2:$F$&Lvolle"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub