AW: Dateneingabe und löschen über Userform
18.12.2014 23:25:32
Mario
allo zusammen,
mein Code sieht bis jetzt so aus:
Option Explicit
Dim Bol As Boolean
Private Sub ComboBox1_Click()
If ComboBox1.ListIndex 0 Then
TextBox1 = Cells(ComboBox1.ListIndex + 2, 2)
TextBox2 = Cells(ComboBox1.ListIndex + 2, 3)
TextBox3 = Cells(ComboBox1.ListIndex + 2, 4)
TextBox4 = Cells(ComboBox1.ListIndex + 2, 5)
Else
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
End If
End Sub
Private Sub CommandButton1_Click()
Dim arr As Variant
Dim zeile As Long
Dim letzte As Long
If ComboBox1.ListIndex > 0 Then
zeile = ComboBox1.ListIndex + 2
letzte = Cells(Rows.Count, 2).End(xlUp).Row
arr = Range("B" & zeile + 1 & ":E" & letzte)
Range("B" & zeile & ":E" & letzte).ClearContents
Range("B" & zeile & ":E" & letzte - 1) = arr
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
UserForm_Initialize
End If
End Sub
Private Sub CommandButton2_Click()
Dim xZeile As Long
If TextBox1 = "" Then Exit Sub
If ComboBox1.ListIndex = 0 Then
xZeile = [B65536].End(xlUp).Row + 2
Else
xZeile = ComboBox1.ListIndex + 2
End If
Cells(xZeile, 2) = TextBox1
Cells(xZeile, 3) = TextBox2
Cells(xZeile, 4) = TextBox3
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
ActiveSheet.Unprotect Password:="9010ml"
ActiveSheet.Range("B3:E200").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect Password:="9010ml"
Range("A1").Select
UserForm_Initialize
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub UserForm_Activate()
'Datum und Uhrzeit anzeigen
Label9.Caption = Date
Bol = True
Do Until Bol = False
DoEvents
Label10.Caption = Time
Loop
End Sub
Private Sub UserForm_Initialize()
Dim aRow, i As Long
Application.EnableEvents = False
ComboBox1.Clear
aRow = [B65536].End(xlUp).Row
ComboBox1.AddItem "Person hinzufügen, ändern oder entfernen!"
For i = 3 To aRow
ComboBox1.AddItem Cells(i, 2) & ", " & Cells(i, 3) & ", " & Cells(i, 4) & ", " & Cells(i, 5) _
Next i
ComboBox1.ListIndex = 0
Application.EnableEvents = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Bol = False
End Sub
Leider bekomme ich immer einen Fehler in der Zeile:
ActiveSheet.Range("B3:E200").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Eventuell könnt ihr mir ja helfen.
VG Mario