Hallo Forum,
ich habe eine UserForm angelegt, mit deren Hilfe Daten aus einer Exceltabelle ausgelesen werden und bearbeitet werden können. Nun würde ich gerne zwei Knöpfe anbringen, die mich nach Auswahl eines Datensatzes aus der ComboBox vor und zurück blättern lassen in den Daten. Ich hänge mal meinen bisherigen Code an, wenn ihr mehr zur Info braucht (Screenshot von der Maske? Exceltabelle?), gebt bitte kurz Bescheid.
Vielen Dank für eure Hilfe. Wenn dann noch zwei Knöpfe realisierbar wären, um zum ersten Datensatz und zum letzten zu gelangen, um so besser!
Dieter
Private Sub CommandButton2_Click()
Workbooks("Kläranlagen_neu.xlsm").Close SaveChanges:=True
End Sub
Private Sub CommandButton3_Click()
UserForm1.PrintForm
End Sub
Private Sub UserForm_Initialize()
Dim aRow, i As Long
Application.EnableEvents = False
ComboBox1.Clear
aRow = [A65536].End(xlUp).Row
ComboBox1.AddItem "neue Person hinzufügen"
For i = 3 To aRow
ComboBox1.AddItem Cells(i, 5) & ", " & Cells(i, 2)
Next i
ComboBox1.ListIndex = 0
Application.EnableEvents = True
End Sub
Private Sub ComboBox1_Click()
If ComboBox1.ListIndex <> 0 Then
TextBox2 = Cells(ComboBox1.ListIndex + 2, 1)
TextBox3 = Cells(ComboBox1.ListIndex + 2, 2)
TextBox4 = Cells(ComboBox1.ListIndex + 2, 3)
TextBox5 = Cells(ComboBox1.ListIndex + 2, 4)
TextBox6 = Cells(ComboBox1.ListIndex + 2, 5)
TextBox7 = Cells(ComboBox1.ListIndex + 2, 6)
TextBox8 = Cells(ComboBox1.ListIndex + 2, 7)
TextBox9 = Cells(ComboBox1.ListIndex + 2, 8)
TextBox10 = Cells(ComboBox1.ListIndex + 2, 9)
TextBox11 = Cells(ComboBox1.ListIndex + 2, 10)
TextBox12 = Cells(ComboBox1.ListIndex + 2, 11)
TextBox13 = Cells(ComboBox1.ListIndex + 2, 12)
TextBox15 = Cells(ComboBox1.ListIndex + 2, 14)
TextBox16 = Cells(ComboBox1.ListIndex + 2, 15)
TextBox17 = Cells(ComboBox1.ListIndex + 2, 16)
TextBox18 = Cells(ComboBox1.ListIndex + 2, 17)
Else
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
End If
End Sub
'Übernehmen
Private Sub CommandButton1_Click()
On Error Resume Next
Dim xZeile As Long
' If TextBox2 = "" Then Exit Sub
If ComboBox1.ListIndex = 0 Then
' letzte Zeile suchen
xZeile = [B65536].End(xlUp).Row + 1
Else
xZeile = ComboBox1.ListIndex + 2
End If
Cells(xZeile, 1) = TextBox2
Cells(xZeile, 2) = TextBox3
Cells(xZeile, 3) = TextBox4
Cells(xZeile, 4) = TextBox5
Cells(xZeile, 5) = TextBox6
Cells(xZeile, 6) = TextBox7
Cells(xZeile, 7) = TextBox8
Cells(xZeile, 8) = TextBox9
Cells(xZeile, 9) = TextBox10
Cells(xZeile, 10) = TextBox11
Cells(xZeile, 11) = TextBox12
Cells(xZeile, 12) = TextBox13
Cells(xZeile, 14) = TextBox15
Cells(xZeile, 15) = TextBox16
Cells(xZeile, 16) = TextBox17
Cells(xZeile, 17) = TextBox18
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
Dim zeilen As Integer
zeilen = Columns(2).End(xlDown).Row
'
' sortieren
'
'
ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle3").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle3").Sort.SortFields. _
Add Key:=Range("E2:E742"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle3").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
UserForm_Initialize
End Sub
|