AW: Listbox
22.01.2006 00:00:04
crissi
Private Sub CommandButton1_Click()
' Artikel erfassen
On Error Resume Next
Dim ANr As String
Dim KText As String
Dim LText As Variant
Dim Menge As Variant
Dim MwSt As Variant
Dim Einh As Variant
Dim EP As Variant
Dim ListNr As Integer
Dim Pos As Integer
Dim EndRow As Integer, A As Integer
Dim iRow As Integer
Dim iRowU As Integer
Dim arr()
' Werte zuordnen
ANr = UserForm1.TextBox3.Value
Einh = UserForm1.ComboBox3.Value
Menge = UserForm1.TextBox4.Value
KText = UserForm1.TextBox5.Value
LText = UserForm1.TextBox6.Value
MwSt = UserForm1.ComboBox7.Value
EP = UserForm1.TextBox7.Value
UserForm1.TextBox100.Value = ""
' Prüfen
Call RechAus
If UserForm1.TextBox101.Value = "Änderungsmodus" Then
Pos = Workbooks("Daten.xls").Sheets("AngDaten").Cells(2, 10)
Else:
Pos = Workbooks("Daten.xls").Sheets("AngDaten").Cells(1, 10)
If Pos = 0 Then Pos = 1
End If
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 1) = ANr
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 2) = Menge
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 3) = Einh
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 4) = KText
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 5) = EP
Workbooks("Daten.xls").Sheets("AngDaten").Cells(Pos, 8) = Pos
Step11:
If UserForm1.TextBox101.Value <> "Änderungsmodus" Then
Pos = Pos + 1
Workbooks("Daten.xls").Sheets("AngDaten").Cells(1, 10) = Pos
End If
' Listbox füllen
UserForm1.ListBox1.Clear
EndRow = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Rows.Count, 2).End(xlUp).Row
For A = 1 To EndRow + 2
If Not IsEmpty(Sheets("AngDaten").Cells(iRow, 1)) Then
ReDim Preserve arr(0 To 5, 0 To iRowU)
arr(0, iRowU) = Workbooks("Daten.xls").Sheets("AngDaten").Cells(A, 1)
arr(1, iRowU) = Workbooks("Daten.xls").Sheets("AngDaten").Cells(A, 2)
arr(1, iRowU) = FormatNumber(arr(1, iRowU), 2)
arr(2, iRowU) = Workbooks("Daten.xls").Sheets("AngDaten").Cells(A, 3)
arr(3, iRowU) = Workbooks("Daten.xls").Sheets("AngDaten").Cells(A, 4)
arr(4, iRowU) = Workbooks("Daten.xls").Sheets("AngDaten").Cells(A, 5)
arr(4, iRowU) = FormatNumber(arr(4, iRowU), 2)
iRowU = iRowU + 1
End If
Next A
UserForm1.ListBox1.Column = arr
UserForm1.ListBox1.ListIndex = iRow - 1
UserForm1.ListBox1.Selected(UserForm1.ListBox1.ListIndex) = False
UserForm1.ComboBox3.Value = ""
UserForm1.ComboBox7.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
Call RechEin
Ende:
End Sub
Private Sub ListBox1_Click()
' Position ändern/löschen
On Error Resume Next
Dim Nr As Integer
Dim Pos As String
Dim Menge As String
Dim Einh As Variant, ANr As Variant
Dim KText As Variant
Dim LText As Variant
Dim EP As Variant
Nr = UserForm1.ListBox1.ListIndex + 1
ANr = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Nr, 1)
Menge = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Nr, 2)
Einh = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Nr, 3)
KText = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Nr, 4)
EP = Workbooks("Daten.xls").Sheets("AngDaten").Cells(Nr, 5)
' Werte zuordnen
If Menge <> "" Then Menge = FormatNumber(Menge, 2)
If EP <> "" Then EP = FormatNumber(EP, 2)
' Ausgabe
UserForm1.ComboBox3.Value = Einh
UserForm1.TextBox3.Value = ANr
UserForm1.TextBox5.Value = KText
UserForm1.TextBox6.Value = LText
If Menge <> "" Then UserForm1.TextBox4.Value = Menge
If EP <> "" Then UserForm1.TextBox7.Value = EP
UserForm1.TextBox101.Value = "Änderungsmodus"
Workbooks("Daten.xls").Sheets("AngDaten").Cells(2, 10) = Nr
End Sub