'''1_1_ LB##########
On Error GoTo EERR
UserForm1.Show
Exit Sub
EERR:
'''1_1_ LB##########
'''2_2_LB##############################################
Sub AAAUUFAF()
On Error GoTo ERR
TBB1.BackColor = &HC0FFFF
TBB2.BackColor = &HC0FFFF
KuNr.Enabled = True
KuNr.BackColor = &HC0FFFF
Dim IC As String
IC = CoB1
If CoB1 > "" Then
Sheets(IC).Activate
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
Dim AAAZ As Variant
Dim AAAC As Variant
POMO.[a2] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
POMO.[a3] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Column
AAAZ = CDbl(POMO.[a2])
AAAC = CDbl(POMO.[a3])
SPALTA1 = ""
SPALTA2 = ""
SPALTA3 = ""
SPALTA4 = ""
SPALTA5 = ""
SPALTA6 = ""
SPALTA7 = ""
SPALTB1 = ""
SPALTB2 = ""
SPALTB3 = ""
SPALTB4 = ""
SPALTB5 = ""
SPALTB6 = ""
SPALTB7 = ""
SPALTC1 = ""
SPALTC2 = ""
SPALTC3 = ""
SPALTC4 = ""
SPALTC5 = ""
SPALTC6 = ""
SPALTC7 = ""
SPALTD1 = ""
SPALTD2 = ""
SPALTD3 = ""
SPALTD4 = ""
SPALTD5 = ""
SPALTD6 = ""
SPALTD7 = ""
SPALTE1 = ""
SPALTE2 = ""
SPALTE3 = ""
SPALTE4 = ""
SPALTE5 = ""
SPALTE6 = ""
SPALTE7 = ""
SPALTF1 = ""
SPALTF2 = ""
SPALTF3 = ""
SPALTF4 = ""
SPALTF5 = ""
SPALTF6 = ""
SPALTF7 = ""
SPALTG1 = ""
SPALTG2 = ""
SPALTG3 = ""
SPALTG4 = ""
SPALTG5 = ""
SPALTG6 = ""
SPALTG7 = ""
SPALTA = ""
SPALTB = ""
SPALTC = ""
SPALTD = ""
SPALTE = ""
SPALTF = ""
SPALTG = ""
KuNr = ""
TBB1.Value = ""
TBB2.Value = ""
TBB3.Value = ""
TBB4.Value = ""
TBB5.Value = ""
TBB6.Value = ""
POMO.[a1] = ""
POMO.[b1] = ""
POMO.[c1] = ""
POMO.[d1] = ""
POMO.[e1] = ""
POMO.[F1] = ""
POMO.[g1] = ""
POMO.[h1] = ""
POMO.[i1] = ""
POMO.[j1] = ""
POMO.[k1] = ""
POMO.[L1] = ""
POMO.[m1] = ""
If POMO.[a2] < 65536 Then
Dim ††† As Variant
If POMO.[a3] = 1 Then
POMO.[a4] = 0
††† = POMO.[a4]
End If
If POMO.[a3] = 7 Then
POMO.[a4] = 6
††† = POMO.[a4]
End If
SPALTA = ActiveSheet.Cells(1, AAAC - †††).Value
SPALTB = ActiveSheet.Cells(1, AAAC + 1).Value
SPALTC = ActiveSheet.Cells(1, AAAC + 2).Value
SPALTD = ActiveSheet.Cells(1, AAAC + 3).Value
SPALTE = ActiveSheet.Cells(1, AAAC + 4).Value
SPALTF = ActiveSheet.Cells(1, AAAC + 5).Value
SPALTG = ActiveSheet.Cells(1, AAAC + 6).Value
If POMO.[a2] > 8 Then
SPALTA1 = ActiveSheet.Cells(AAAZ - 6, AAAC - †††).Value
SPALTB1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 1).Value
SPALTC1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 2).Value
SPALTD1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 3).Value
SPALTE1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 4).Value
SPALTF1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 5).Value
SPALTG1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 6).Value
End If
If POMO.[a2] > 7 Then
SPALTA2 = ActiveSheet.Cells(AAAZ - 5, AAAC - †††).Value
SPALTB2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 1).Value
SPALTC2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 2).Value
SPALTD2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 3).Value
SPALTE2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 4).Value
SPALTF2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 5).Value
SPALTG2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 6).Value
End If
If POMO.[a2] > 6 Then
SPALTA3 = ActiveSheet.Cells(AAAZ - 4, AAAC - †††).Value
SPALTB3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 1).Value
SPALTC3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 2).Value
SPALTD3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 3).Value
SPALTE3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 4).Value
SPALTF3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 5).Value
SPALTG3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 6).Value
End If
If POMO.[a2] > 5 Then
SPALTA4 = ActiveSheet.Cells(AAAZ - 3, AAAC - †††).Value
SPALTB4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 1).Value
SPALTC4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 2).Value
SPALTD4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 3).Value
SPALTE4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 4).Value
SPALTF4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 5).Value
SPALTG4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 6).Value
End If
If POMO.[a2] > 4 Then
SPALTA5 = ActiveSheet.Cells(AAAZ - 2, AAAC - †††).Value
SPALTB5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 1).Value
SPALTC5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 2).Value
SPALTD5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 3).Value
SPALTE5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 4).Value
SPALTF5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 5).Value
SPALTG5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 6).Value
End If
If POMO.[a2] > 3 Then
SPALTA6 = ActiveSheet.Cells(AAAZ - 1, AAAC - †††).Value
SPALTB6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 1).Value
SPALTC6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 2).Value
SPALTD6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 3).Value
SPALTE6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 4).Value
SPALTF6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 5).Value
SPALTG6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 6).Value
End If
If POMO.[a2] > 2 Then
SPALTA7 = ActiveSheet.Cells(AAAZ, AAAC - †††).Value
SPALTB7 = ActiveSheet.Cells(AAAZ, AAAC + 1).Value
SPALTC7 = ActiveSheet.Cells(AAAZ, AAAC + 2).Value
SPALTD7 = ActiveSheet.Cells(AAAZ, AAAC + 3).Value
SPALTE7 = ActiveSheet.Cells(AAAZ, AAAC + 4).Value
SPALTF7 = ActiveSheet.Cells(AAAZ, AAAC + 5).Value
SPALTG7 = ActiveSheet.Cells(AAAZ, AAAC + 6).Value
End If
End If
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
TANA = ActiveSheet.Name
End If
Exit Sub
ERR:
End Sub
Private Sub CheckBox1_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox1 = True Then
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox5 = False
CheckBox6 = False
CommandButton8.Visible = False
With ListBox1
.ColumnCount = 6
.ColumnHeads = True
.ColumnWidths = 100 & ";" & 120 & ";" & 80 & ";" & 70 & ";" & 70 & ";" & 140
.BackColor = &HFFFF&
End With
With LLLNNN2
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 6)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If TextBox7.Value = "2" Or TextBox7.Value = "3" Then
If ComboBox4.Value <> "" Then
ListBox1.ListIndex = ComboBox4.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
If TextBox7.Value = "8" Or TextBox7.Value = "9" Then
If ComboBox8.Value <> "" Then
ListBox1.ListIndex = ComboBox8.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
End If
End Sub
Private Sub CheckBox2_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox2 = True Then
CheckBox1 = False
CheckBox3 = False
CheckBox4 = False
CheckBox5 = False
CheckBox6 = False
CommandButton8.Visible = True
With ListBox1
.ColumnCount = 13
.ColumnHeads = True
.ColumnWidths = 60 & ";" & 70 & ";" & 100 & ";" & 100 & ";" & 120 & ";" & 70 & ";" & 70 & ";" & 70 & ";" & 70 & ";" & 100 & ";" & 100 & ";" & 70 & ";" & 70
.BackColor = &HFFFFFF
End With
With LLLNNN3
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 13)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox1.Value <> "" Then
ListBox1.ListIndex = ComboBox1.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
End Sub
Private Sub CheckBox3_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox3 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox4 = False
CheckBox5 = False
CheckBox6 = False
CommandButton8.Visible = False
With ListBox1
.ColumnCount = 9
.ColumnHeads = True
.ColumnWidths = 70 & ";" & 70 & ";" & 100 & ";" & 100 & ";" & 120 & ";" & 70 & ";" & 70 & ";" & 70 & ";" & 100
.BackColor = &H80FF80
End With
With LLLNNN4
ListBox1.RowSource = .Range(.Cells(16, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 9)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox7.Value <> "" Then
ListBox1.ListIndex = ComboBox7.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
End Sub
Private Sub CheckBox4_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox4 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox5 = False
CheckBox6 = False
CommandButton8.Visible = True
With ListBox1
.ColumnCount = 201
.ColumnHeads = True
.BackColor = &H80C0FF
End With
With LLLNNN5
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 201)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox6.Value <> "" Then
ListBox1.ListIndex = ComboBox6.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
End Sub
Private Sub CheckBox5_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox5 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
CommandButton8.Visible = True
With ListBox1
.ColumnCount = 4
.ColumnHeads = True
.ColumnWidths = 140 & ";" & 140 & ";" & 140 & ";" & 100
.BackColor = &HE0E0E0
End With
With LLLNNN2
ListBox1.RowSource = .Range(.Cells(2, 7), .Cells(.Cells(Rows.Count, 7).End(xlUp).Row, 10)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox5.Value <> "" Then
ListBox1.ListIndex = ComboBox5.ListIndex
End If
If ComboBox10.Value <> "" Then
ListBox1.ListIndex = ComboBox10.ListIndex
End If
End If
End Sub
Private Sub CheckBox6_Click()
On Error Resume Next
Dim AAAA As Long
If CheckBox6 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox5 = False
CommandButton8.Visible = False
End If
If LLLNNN6.[e1] <> "" And LLLNNN6.[n1] <> "" Then
With ListBox1
.ColumnCount = 201
.ColumnHeads = True
.BackColor = &H80C0FF
End With
With LLLNNN6
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 201)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox6.Value <> "" Then
ListBox1.ListIndex = ComboBox6.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
If LLLNNN6.[e1] <> "" And LLLNNN6.[n1] = "" Then
With ListBox1
.ColumnCount = 13
.ColumnHeads = True
.ColumnWidths = 60 & ";" & 70 & ";" & 100 & ";" & 100 & ";" & 120 & ";" & 70 & ";" & 70 & ";" & 70 & ";" & 70 & ";" & 100 & ";" & 100 & ";" & 70 & ";" & 70
.BackColor = &HFFFFFF
End With
With LLLNNN6
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 13)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox1.Value <> "" Then
ListBox1.ListIndex = ComboBox1.ListIndex
Else:
ListBox1.ListIndex = ListBox1.ListCount - 1
End If
End If
If LLLNNN6.[e1] = "" Then
With ListBox1
.ColumnCount = 4
.ColumnHeads = True
.ColumnWidths = 140 & ";" & 140 & ";" & 140 & ";" & 100
.BackColor = &HE0E0E0
End With
With LLLNNN6
ListBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 13)).Address(External:=True)
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox5.Value <> "" Then
ListBox1.ListIndex = ComboBox5.ListIndex
End If
If ComboBox10.Value <> "" Then
ListBox1.ListIndex = ComboBox10.ListIndex
End If
End If
End Sub
Private Sub ComboBox1_Change()
On Error GoTo EERR
Dim ††† As Long
CommandButton4.Visible = False
If ComboBox1.Value <> "" Then
CommandButton4.Visible = True
CommandButton1.Visible = False
End If
If ComboBox1.Value = "" Then
CommandButton1.Visible = True
End If
If TextBox7.Value = "1" Then
If ComboBox1.Value <> "" Then
††† = CDbl(LLLNNN3.Range("a2:a1048500").Find(What:=ComboBox1.Value, lookat:=xlWhole).Row)
If LLLNNN3.Cells(†††, 2) <> "" Then
TextBox1.Value = CDate(LLLNNN3.Cells(†††, 2))
End If
ComboBox2.Value = LLLNNN3.Cells(†††, 3).Value
ComboBox3.Value = LLLNNN3.Cells(†††, 4).Value
ComboBox4.Value = LLLNNN3.Cells(†††, 5).Value
Label12.Caption = LLLNNN3.Cells(†††, 6).Value
Label13.Caption = LLLNNN3.Cells(†††, 7).Value
TextBox2.Value = LLLNNN3.Cells(†††, 8).Value
TextBox3.Value = LLLNNN3.Cells(†††, 9).Value
ComboBox5.Value = LLLNNN3.Cells(†††, 10).Value
TextBox4.Value = LLLNNN3.Cells(†††, 11).Value
End If
End If
Call CheckBox2_Click
Exit Sub
EERR:
TextBox1.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
Label12.Caption = ""
Label13.Caption = ""
TextBox2.Value = ""
TextBox3.Value = ""
ComboBox5.Value = ""
TextBox4.Value = ""
End Sub
Private Sub ComboBox1_Enter()
On Error GoTo EERR
TextBox7.Value = ""
TextBox7.Value = "1"
CheckBox1 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
CheckBox2 = True
Call CheckBox2_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox1.Value <> "" Then
††† = CDbl(LLLNNN3.Range("a2:a1048500").Find(What:=ComboBox1.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox1.Value = ""
End Sub
Private Sub ComboBox10_Change()
On Error GoTo EERR
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox10.Value <> "" Then
ListBox1.ListIndex = ComboBox10.ListIndex
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox10_Enter()
On Error GoTo EERR
CheckBox5 = False
CheckBox5 = True
ComboBox5.Value = ""
Exit Sub
EERR:
End Sub
Private Sub ComboBox10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox10.Value <> "" Then
††† = CDbl(LLLNNN2.Range("g2:g1048500").Find(What:=ComboBox10.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox10.Value = ""
End Sub
Private Sub ComboBox2_Enter()
On Error GoTo EERR
CheckBox2 = False
CheckBox2 = True
Exit Sub
EERR:
End Sub
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If ComboBox2.Value <> "Zugang" And ComboBox2.Value > "Bezugskosten" And ComboBox2.Value <> "Gutschrift/Nachlaesse" Then
ComboBox2.Value = ""
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox3_Change()
On Error GoTo EERR
Dim ††† As Long
If TextBox7.Value = "2" Or TextBox7.Value = "1" Then
ComboBox8.Value = ""
ComboBox9.Value = ""
Label24.Caption = ""
Label25.Caption = ""
ComboBox4.Value = ""
Label12.Caption = ""
Label13.Caption = ""
If ComboBox3.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox3.Value, lookat:=xlWhole).Row)
ComboBox4.Value = LLLNNN2.Cells(†††, 2).Value
Label12.Caption = LLLNNN2.Cells(†††, 3).Value
Label13.Caption = LLLNNN2.Cells(†††, 6).Value
End If
End If
Call CheckBox1_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox3_Enter()
On Error GoTo EERR
TextBox7.Value = ""
TextBox7.Value = "2"
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
Call CheckBox1_Click
If ComboBox3.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox3.Value, lookat:=xlWhole).Row)
ComboBox4.Value = LLLNNN2.Cells(†††, 2).Value
Label12.Caption = LLLNNN2.Cells(†††, 3).Value
Label13.Caption = LLLNNN2.Cells(†††, 6).Value
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox3.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox3.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox3.Value = ""
End Sub
Private Sub ComboBox4_Change()
On Error GoTo EERR
Dim ††† As Long
If TextBox7.Value = "3" Then
ComboBox3.Value = ""
ComboBox8.Value = ""
ComboBox9.Value = ""
Label24.Caption = ""
Label25.Caption = ""
Label12.Caption = ""
Label13.Caption = ""
If ComboBox4.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox4.Value, lookat:=xlWhole).Row)
ComboBox3.Value = LLLNNN2.Cells(†††, 1).Value
Label12.Caption = LLLNNN2.Cells(†††, 3).Value
Label13.Caption = LLLNNN2.Cells(†††, 6).Value
End If
End If
Call CheckBox1_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox4_Enter()
On Error GoTo EERR
TextBox7.Value = ""
TextBox7.Value = "3"
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
Call CheckBox1_Click
If ComboBox4.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox4.Value, lookat:=xlWhole).Row)
ComboBox3.Value = LLLNNN2.Cells(†††, 1).Value
Label12.Caption = LLLNNN2.Cells(†††, 3).Value
Label13.Caption = LLLNNN2.Cells(†††, 6).Value
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox4.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox4.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox4.Value = ""
End Sub
Private Sub ComboBox5_Change()
On Error GoTo EERR
ListBox1.ListIndex = ListBox1.ListCount - 1
If ComboBox5.Value <> "" Then
ListBox1.ListIndex = ComboBox5.ListIndex
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox5_Enter()
On Error GoTo EERR
CheckBox5 = False
CheckBox5 = True
ComboBox10.Value = ""
Exit Sub
EERR:
End Sub
Private Sub ComboBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox5.Value <> "" Then
††† = CDbl(LLLNNN2.Range("g2:g1048500").Find(What:=ComboBox5.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox5.Value = ""
End Sub
Private Sub ComboBox6_Change()
On Error GoTo EERR
Dim AAAC As Long
Dim SCHOT As Long
Dim ††† As Long
ComboBox7.Clear
ComboBox7.Value = ""
LLLNNN4.Range("a16:i35") = ""
If TextBox7.Value = "6" Then
If ComboBox6.Value <> "" Then
LLLNNN4.Cells(13, 1) = "Entnahmeschein"
LLLNNN4.Cells(13, 4) = "Nr.:"
LLLNNN4.Cells(13, 5) = ComboBox6.Value
LLLNNN4.Cells(15, 1) = "Position-Nr"
LLLNNN4.Cells(15, 2) = "Datum"
LLLNNN4.Cells(15, 3) = "Artikel-Nr."
LLLNNN4.Cells(15, 4) = "Bezeichnung"
LLLNNN4.Cells(15, 5) = "Einheit"
LLLNNN4.Cells(15, 6) = "Preis"
LLLNNN4.Cells(15, 7) = "Menge"
LLLNNN4.Cells(15, 8) = "Betrag"
LLLNNN4.Cells(15, 9) = "Lagerplatz"
TextBox8.Value = ComboBox6.Value
††† = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=ComboBox6.Value, lookat:=xlWhole).Row)
For SCHOT = 0 To 19
AAAC = 2 + SCHOT * 10
If LLLNNN5.Cells(†††, AAAC).Value <> "" And LLLNNN5.Cells(†††, AAAC).Value <> "_" Then
With ComboBox7
.AddItem LLLNNN5.Cells(†††, AAAC).Value
End With
End If
Next SCHOT
CheckBox4 = True
Call CheckBox4_Click
End If
End If
Exit Sub
EERR:
TextBox5.Value = ""
ComboBox8.Value = ""
ComboBox4.Value = ""
TextBox6.Value = ""
Label26.Caption = ""
ComboBox5.Value = ""
ComboBox10.Value = ""
End Sub
Private Sub ComboBox6_Enter()
On Error GoTo EERR
TextBox7.Value = ""
TextBox7.Value = "6"
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox6 = False
CheckBox4 = True
Call CheckBox4_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
††† = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=ComboBox6.Value, lookat:=xlWhole).Row)
Exit Sub
EERR:
ComboBox6.Value = ""
End Sub
Private Sub ComboBox7_Change()
On Error GoTo EERR
Dim AAAC As Long
Dim ††† As Long
ComboBox1.Value = ""
CommandButton2.Visible = True
If ComboBox6.Value <> "" Then
If ComboBox7.Value <> "" Then
††† = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=ComboBox6.Value, lookat:=xlWhole).Row)
AAAC = (CDbl(ComboBox7.Value) - 1) * 10 + 1
ComboBox1.Value = LLLNNN5.Cells(†††, AAAC + 10).Value
CommandButton2.Visible = False
If LLLNNN5.Cells(†††, AAAC + 1) <> "" Then
TextBox5.Value = CDate(LLLNNN5.Cells(†††, AAAC + 2))
End If
ComboBox8.Value = LLLNNN5.Cells(†††, AAAC + 3).Value
ComboBox9.Value = LLLNNN5.Cells(†††, AAAC + 4).Value
Label24.Caption = LLLNNN5.Cells(†††, AAAC + 5).Value
Label25.Caption = LLLNNN5.Cells(†††, AAAC + 6).Value
TextBox6.Value = LLLNNN5.Cells(†††, AAAC + 7).Value
Label26.Caption = LLLNNN5.Cells(†††, AAAC + 8).Value
ComboBox10.Value = LLLNNN5.Cells(†††, AAAC + 9).Value
End If
End If
CheckBox3 = False
CheckBox3 = True
Exit Sub
EERR:
End Sub
Private Sub ComboBox7_Enter()
On Error GoTo EERR
Dim AAAC As Long
Dim SCHOT As Long
Dim ††† As Long
Dim AAAR As Long
Dim PNPN As Long
TextBox7.Value = "7"
CheckBox1 = False
CheckBox2 = False
CheckBox4 = False
CheckBox6 = False
If ComboBox6.Value <> "" Then
If TextBox8.Value <> LLLNNN4.Cells(13, 5) Then
††† = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=ComboBox6.Value, lookat:=xlWhole).Row)
AAAR = 16
For SCHOT = 0 To 19
AAAC = 2 + SCHOT * 10
If LLLNNN5.Cells(†††, AAAC).Value <> "" And LLLNNN5.Cells(†††, AAAC).Value <> "_" Then
For PNPN = 0 To 8
LLLNNN4.Cells(AAAR, 1 + PNPN) = LLLNNN5.Cells(†††, AAAC + PNPN)
Next PNPN
AAAR = AAAR + 1
End If
Next SCHOT
End If
End If
CheckBox3 = False
CheckBox3 = True
Exit Sub
EERR:
End Sub
Private Sub ComboBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
Dim AAAC As Long
Dim KKKK As Long
Dim SCHOT As Long
If ComboBox6.Value <> "" Then
If ComboBox7.Value <> "" Then
††† = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=ComboBox6.Value, lookat:=xlWhole).Row)
KKKK = 0
For SCHOT = 0 To 19
AAAC = 2 + SCHOT * 10
If LLLNNN5.Cells(†††, AAAC) = CDbl(ComboBox7.Value) Then
KKKK = KKKK + 1
End If
Next SCHOT
If KKKK = 0 Then
ComboBox7.Value = ""
End If
End If
End If
Exit Sub
EERR:
ComboBox7.Value = ""
End Sub
Private Sub ComboBox8_Change()
On Error GoTo EERR
Dim ††† As Long
If TextBox7.Value = "8" Or TextBox7.Value = "7" Then
ComboBox3.Value = ""
ComboBox4.Value = ""
Label12.Caption = ""
Label13.Caption = ""
ComboBox9.Value = ""
Label24.Caption = ""
Label25.Caption = ""
If ComboBox8.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox8.Value, lookat:=xlWhole).Row)
ComboBox9.Value = LLLNNN2.Cells(†††, 2).Value
Label24.Caption = LLLNNN2.Cells(†††, 3).Value
Label25.Caption = LLLNNN2.Cells(†††, 6).Value
End If
End If
Call CheckBox1_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox8_Enter()
On Error GoTo EERR
Dim ††† As Long
TextBox7.Value = ""
TextBox7.Value = "8"
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
Call CheckBox1_Click
If ComboBox8.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox8.Value, lookat:=xlWhole).Row)
ComboBox9.Value = LLLNNN2.Cells(†††, 2).Value
Label24.Caption = LLLNNN2.Cells(†††, 3).Value
Label25.Caption = LLLNNN2.Cells(†††, 6).Value
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox8.Value <> "" Then
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox8.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox8.Value = ""
End Sub
Private Sub ComboBox9_Change()
On Error GoTo EERR
Dim ††† As Long
If TextBox7.Value = "9" Then
ComboBox8.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
Label12.Caption = ""
Label13.Caption = ""
Label24.Caption = ""
Label25.Caption = ""
If ComboBox9.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox9.Value, lookat:=xlWhole).Row)
ComboBox8.Value = LLLNNN2.Cells(†††, 1).Value
Label24.Caption = LLLNNN2.Cells(†††, 3).Value
Label25.Caption = LLLNNN2.Cells(†††, 6).Value
End If
End If
Call CheckBox1_Click
Exit Sub
EERR:
End Sub
Private Sub ComboBox9_Enter()
On Error GoTo EERR
TextBox7.Value = ""
TextBox7.Value = "9"
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox6 = False
Call CheckBox1_Click
If ComboBox9.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox9.Value, lookat:=xlWhole).Row)
ComboBox8.Value = LLLNNN2.Cells(†††, 1).Value
Label24.Caption = LLLNNN2.Cells(†††, 3).Value
Label25.Caption = LLLNNN2.Cells(†††, 6).Value
End If
Exit Sub
EERR:
End Sub
Private Sub ComboBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
Dim ††† As Long
If ComboBox9.Value <> "" Then
††† = CDbl(LLLNNN2.Range("b2:b1048500").Find(What:=ComboBox9.Value, lookat:=xlWhole).Row)
End If
Exit Sub
EERR:
ComboBox9.Value = ""
End Sub
Private Sub CommandButton1_Click()
On Error Resume Next
Dim ††† As Long
Dim KKKK As Long
Dim SCHOT As Long
Dim AAAR As Long
Dim AAAL As Long
Dim SLOWO As String
TextBox1.SetFocus
ComboBox2.SetFocus
TextBox3.SetFocus
TextBox4.SetFocus
If ComboBox2.Value = "" Then
MsgBox "Geschaeftsfall ist nicht ausgewaehlt!", 48, "https://excel.hpage.de "
ComboBox2.SetFocus
Exit Sub
End If
If ComboBox3.Value = "" Then
MsgBox "Artikel-Nr. ist nicht ausgewaehlt!", 48, "https://excel.hpage.de "
ComboBox3.SetFocus
Exit Sub
End If
If TextBox2.Value = "" Then
TextBox2.Value = 0
End If
If TextBox3.Value = "" Then
TextBox3.Value = 0
End If
If ComboBox5.Value = "" Then
MsgBox "Lagerplatz ist nicht ausgewaehlt!", 48, "https://excel.hpage.de "
ComboBox5.SetFocus
Exit Sub
End If
AAAL = CDbl(LLLNNN2.Range("g2:g1048500").Find(What:=ComboBox5.Value, lookat:=xlWhole).Row)
If LLLNNN2.Cells(AAAL, 8) <> "" And LLLNNN2.Cells(AAAL, 8) <> ComboBox3.Value And LLLNNN2.Cells(AAAL, 8) <> "_" Then
MsgBox "Auf dem gewählten Lagerplatz ist schon anderer Artikel gebucht!", 48, "https://excel.hpage.de "
ComboBox5.SetFocus
Exit Sub
End If
If ComboBox1.Value = "" Then
KKKK = 0
For SCHOT = 2 To 1048501
If KKKK = 0 Then
If LLLNNN3.Cells(SCHOT, 1).Value = "" Or LLLNNN3.Cells(SCHOT, 1).Value = "_" Then
KKKK = LLLNNN3.Cells(SCHOT, 1).Row
End If
End If
Next SCHOT
††† = KKKK
End If
If ComboBox1.Value = "" Then
AAAR = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox3.Value, lookat:=xlWhole).Row)
If ††† > 1048500 Then
MsgBox "Grundbuch ist voll!", 48, "https://excel.hpage.de "
ComboBox1.SetFocus
Exit Sub
End If
LLLNNN3.Cells(†††, 1) = ††† - 1
If TextBox1.Value = "" Then
LLLNNN3.Cells(†††, 2) = Date
Else:
LLLNNN3.Cells(†††, 2) = CDate(TextBox1.Value)
End If
LLLNNN3.Cells(†††, 3) = ComboBox2.Value
LLLNNN3.Cells(†††, 4) = ComboBox3.Value
LLLNNN3.Cells(†††, 5) = ComboBox4.Value
LLLNNN3.Cells(†††, 6) = Label12.Caption
If ComboBox2.Value = "Zugang" Then
LLLNNN3.Cells(†††, 8) = CDbl(TextBox2.Value)
LLLNNN3.Cells(†††, 9) = CDbl(TextBox3.Value)
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAR, 4) = LLLNNN2.Cells(AAAR, 4) + CDbl(TextBox2.Value)
LLLNNN2.Cells(AAAR, 5) = Round(LLLNNN2.Cells(AAAR, 5) + CDbl(TextBox3.Value), 2)
LLLNNN2.Cells(AAAR, 6) = Round(LLLNNN2.Cells(AAAR, 5) / LLLNNN2.Cells(AAAR, 4), 2)
LLLNNN2.Protect Password:="3"
End If
If ComboBox2.Value = "Bezugskosten" Then
LLLNNN3.Cells(†††, 8) = 0
LLLNNN3.Cells(†††, 9) = CDbl(TextBox3.Value)
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAR, 4) = LLLNNN2.Cells(AAAR, 4) + 0
LLLNNN2.Cells(AAAR, 5) = Round(LLLNNN2.Cells(AAAR, 5) + CDbl(TextBox3.Value), 2)
LLLNNN2.Cells(AAAR, 6) = Round(LLLNNN2.Cells(AAAR, 5) / LLLNNN2.Cells(AAAR, 4), 2)
LLLNNN2.Protect Password:="3"
End If
If ComboBox2.Value = "Gutschrift/Nachlaesse" Then
LLLNNN3.Cells(†††, 8) = 0
LLLNNN3.Cells(†††, 9) = CDbl(TextBox3.Value) * -1
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAR, 4) = LLLNNN2.Cells(AAAR, 4) + 0
LLLNNN2.Cells(AAAR, 5) = Round(LLLNNN2.Cells(AAAR, 5) - CDbl(TextBox3.Value), 2)
LLLNNN2.Cells(AAAR, 6) = Round(LLLNNN2.Cells(AAAR, 5) / LLLNNN2.Cells(AAAR, 4), 2)
LLLNNN2.Protect Password:="3"
End If
LLLNNN3.Cells(†††, 7) = LLLNNN2.Cells(AAAR, 6)
LLLNNN3.Cells(†††, 10) = ComboBox5.Value
LLLNNN3.Cells(†††, 11) = TextBox4.Value
LLLNNN3.Cells(†††, 12) = "_"
LLLNNN3.Cells(†††, 13) = "_"
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAL, 8) = ComboBox3.Value
LLLNNN2.Cells(AAAL, 9) = ComboBox4.Value
LLLNNN2.Cells(AAAL, 10) = LLLNNN2.Cells(AAAL, 10) + LLLNNN3.Cells(†††, 8)
LLLNNN2.Protect Password:="3"
With LLLNNN2
ComboBox3.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
ComboBox4.RowSource = .Range(.Cells(2, 2), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 2)).Address(External:=True)
End With
With LLLNNN3
ComboBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
End With
SLOWO = ComboBox3.Value
ComboBox3.SetFocus
ComboBox3.Value = ""
ComboBox3.Value = SLOWO
ComboBox1.SetFocus
ComboBox1.Value = ††† - 1
ComboBox1.SetFocus
Exit Sub
End If
Exit Sub
End Sub
Sub COAUUFAF()
On Error GoTo ERR
TBB1.BackColor = &HC0FFFF
TBB2.BackColor = &HC0FFFF
KuNr.Enabled = True
KuNr.BackColor = &HC0FFFF
Dim IC As String
IC = CoB1
If CoB1 > "" Then
Sheets(IC).Activate
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
Dim AAAZ As Variant
Dim AAAC As Variant
POMO.[a2] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
POMO.[a3] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Column
AAAZ = CDbl(POMO.[a2])
AAAC = CDbl(POMO.[a3])
SPALTA1 = ""
SPALTA2 = ""
SPALTA3 = ""
SPALTA4 = ""
SPALTA5 = ""
SPALTA6 = ""
SPALTA7 = ""
SPALTB1 = ""
SPALTB2 = ""
SPALTB3 = ""
SPALTB4 = ""
SPALTB5 = ""
SPALTB6 = ""
SPALTB7 = ""
SPALTC1 = ""
SPALTC2 = ""
SPALTC3 = ""
SPALTC4 = ""
SPALTC5 = ""
SPALTC6 = ""
SPALTC7 = ""
SPALTD1 = ""
SPALTD2 = ""
SPALTD3 = ""
SPALTD4 = ""
SPALTD5 = ""
SPALTD6 = ""
SPALTD7 = ""
SPALTE1 = ""
SPALTE2 = ""
SPALTE3 = ""
SPALTE4 = ""
SPALTE5 = ""
SPALTE6 = ""
SPALTE7 = ""
SPALTF1 = ""
SPALTF2 = ""
SPALTF3 = ""
SPALTF4 = ""
SPALTF5 = ""
SPALTF6 = ""
SPALTF7 = ""
SPALTG1 = ""
SPALTG2 = ""
SPALTG3 = ""
SPALTG4 = ""
SPALTG5 = ""
SPALTG6 = ""
SPALTG7 = ""
SPALTA = ""
SPALTB = ""
SPALTC = ""
SPALTD = ""
SPALTE = ""
SPALTF = ""
SPALTG = ""
KuNr = ""
TBB1.Value = ""
TBB2.Value = ""
TBB3.Value = ""
TBB4.Value = ""
TBB5.Value = ""
TBB6.Value = ""
POMO.[a1] = ""
POMO.[b1] = ""
POMO.[c1] = ""
POMO.[d1] = ""
POMO.[e1] = ""
POMO.[F1] = ""
POMO.[g1] = ""
POMO.[h1] = ""
POMO.[i1] = ""
POMO.[j1] = ""
POMO.[k1] = ""
POMO.[L1] = ""
POMO.[m1] = ""
If POMO.[a2] < 65536 Then
Dim ††† As Variant
If POMO.[a3] = 1 Then
POMO.[a4] = 0
††† = POMO.[a4]
End If
If POMO.[a3] = 7 Then
POMO.[a4] = 6
††† = POMO.[a4]
End If
SPALTA = ActiveSheet.Cells(1, AAAC - †††).Value
SPALTB = ActiveSheet.Cells(1, AAAC + 1).Value
SPALTC = ActiveSheet.Cells(1, AAAC + 2).Value
SPALTD = ActiveSheet.Cells(1, AAAC + 3).Value
SPALTE = ActiveSheet.Cells(1, AAAC + 4).Value
SPALTF = ActiveSheet.Cells(1, AAAC + 5).Value
SPALTG = ActiveSheet.Cells(1, AAAC + 6).Value
If POMO.[a2] > 8 Then
SPALTA1 = ActiveSheet.Cells(AAAZ - 6, AAAC - †††).Value
SPALTB1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 1).Value
SPALTC1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 2).Value
SPALTD1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 3).Value
SPALTE1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 4).Value
SPALTF1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 5).Value
SPALTG1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 6).Value
End If
If POMO.[a2] > 7 Then
SPALTA2 = ActiveSheet.Cells(AAAZ - 5, AAAC - †††).Value
SPALTB2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 1).Value
SPALTC2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 2).Value
SPALTD2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 3).Value
SPALTE2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 4).Value
SPALTF2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 5).Value
SPALTG2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 6).Value
End If
If POMO.[a2] > 6 Then
SPALTA3 = ActiveSheet.Cells(AAAZ - 4, AAAC - †††).Value
SPALTB3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 1).Value
SPALTC3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 2).Value
SPALTD3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 3).Value
SPALTE3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 4).Value
SPALTF3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 5).Value
SPALTG3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 6).Value
End If
If POMO.[a2] > 5 Then
SPALTA4 = ActiveSheet.Cells(AAAZ - 3, AAAC - †††).Value
SPALTB4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 1).Value
SPALTC4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 2).Value
SPALTD4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 3).Value
SPALTE4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 4).Value
SPALTF4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 5).Value
SPALTG4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 6).Value
End If
If POMO.[a2] > 4 Then
SPALTA5 = ActiveSheet.Cells(AAAZ - 2, AAAC - †††).Value
SPALTB5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 1).Value
SPALTC5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 2).Value
SPALTD5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 3).Value
SPALTE5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 4).Value
SPALTF5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 5).Value
SPALTG5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 6).Value
End If
If POMO.[a2] > 3 Then
SPALTA6 = ActiveSheet.Cells(AAAZ - 1, AAAC - †††).Value
SPALTB6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 1).Value
SPALTC6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 2).Value
SPALTD6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 3).Value
SPALTE6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 4).Value
SPALTF6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 5).Value
SPALTG6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 6).Value
End If
If POMO.[a2] > 2 Then
SPALTA7 = ActiveSheet.Cells(AAAZ, AAAC - †††).Value
SPALTB7 = ActiveSheet.Cells(AAAZ, AAAC + 1).Value
SPALTC7 = ActiveSheet.Cells(AAAZ, AAAC + 2).Value
SPALTD7 = ActiveSheet.Cells(AAAZ, AAAC + 3).Value
SPALTE7 = ActiveSheet.Cells(AAAZ, AAAC + 4).Value
SPALTF7 = ActiveSheet.Cells(AAAZ, AAAC + 5).Value
SPALTG7 = ActiveSheet.Cells(AAAZ, AAAC + 6).Value
End If
End If
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
TANA = ActiveSheet.Name
End If
Exit Sub
ERR:
End Sub
Private Sub CommandButton2_Click()
On Error Resume Next
Dim AAAC As Long
Dim PPPP As Long
Dim ††† As Long
Dim LLLZ As Long
Dim LLLC As Long
Dim GGGZ As Long
Dim KKKK As Long
Dim SCHOT As Long
Dim AAAL As Long
Dim AAAR As Long
If ComboBox6.Value = "_" Then
ComboBox6.Value = ""
End If
KKKK = 0
For SCHOT = 2 To 1048501
If KKKK = 0 Then
If LLLNNN3.Cells(SCHOT, 1).Value = "" Or LLLNNN3.Cells(SCHOT, 1).Value = "_" Then
KKKK = LLLNNN3.Cells(SCHOT, 1).Row
End If
End If
Next SCHOT
AAAR = KKKK
If AAAR > 1048500 Then
MsgBox "Grundbuch ist voll!", 48, "https://excel.hpage.de "
ComboBox6.SetFocus
Exit Sub
End If
If ComboBox8.Value = "" Then
MsgBox "Artikel-Nr. ist nicht ausgewaehlt!", 48, "https://excel.hpage.de "
ComboBox8.SetFocus
Exit Sub
End If
ComboBox8.SetFocus
TextBox6.SetFocus
ComboBox10.SetFocus
††† = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=ComboBox8.Value, lookat:=xlWhole).Row)
If LLLNNN2.Cells(†††, 4) = "" Or LLLNNN2.Cells(†††, 4) = 0 Or LLLNNN2.Cells(†††, 4) < 0 Then
MsgBox "Artikel hat kein Bestand!", 48, "https://excel.hpage.de "
TextBox6.SetFocus
Exit Sub
End If
If TextBox6.Value = "" Then
MsgBox "Menge ist nicht eingetragen!", 48, "https://excel.hpage.de "
TextBox6.SetFocus
Exit Sub
End If
If Label26.Caption = "" Then
MsgBox "Betrag fehlt!", 48, "https://excel.hpage.de "
Exit Sub
End If
If ComboBox10.Value = "" Then
MsgBox "Lagerplatz ist nicht ausgewaehlt!", 48, "https://excel.hpage.de "
ComboBox10.SetFocus
Exit Sub
End If
AAAL = CDbl(LLLNNN2.Range("g2:g1048500").Find(What:=ComboBox10.Value, lookat:=xlWhole).Row)
If LLLNNN2.Cells(AAAL, 10) <= 0 Then
MsgBox "Auf dem gewählten Lagerplatz gibt es kein gewählter Artikel!", 48, "https://excel.hpage.de "
ComboBox10.SetFocus
Exit Sub
End If
If LLLNNN2.Cells(AAAL, 10) < CDbl(TextBox6.Value) Then
MsgBox "Auf dem gewählten Lagerplatz gibt es zu wenig Artikel!", 48, "https://excel.hpage.de "
TextBox6.SetFocus
Exit Sub
End If
If LLLNNN2.Cells(AAAL, 8) = "" Or LLLNNN2.Cells(AAAL, 8) = "_" Then
MsgBox "Auf dem gewählten Lagerplatz gibt es kein Artikel!", 48, "https://excel.hpage.de "
ComboBox10.SetFocus
Exit Sub
End If
If LLLNNN2.Cells(AAAL, 8) <> ComboBox8.Value Then
MsgBox "Auf dem gewählten Lagerplatz gibt es kein gewählter Artikel!", 48, "https://excel.hpage.de "
ComboBox10.SetFocus
Exit Sub
End If
If LLLNNN2.Cells(†††, 4) - CDbl(TextBox6.Value) < 0 Then
MsgBox "Menge ist groeßer als Bestand!", 48, "https://excel.hpage.de "
TextBox6.SetFocus
Exit Sub
End If
If ComboBox6.Value = "" Then
KKKK = 0
For SCHOT = 2 To 1048501
If KKKK = 0 Then
If LLLNNN5.Cells(SCHOT, 1).Value = "" Or LLLNNN5.Cells(SCHOT, 1).Value = "_" Then
KKKK = LLLNNN5.Cells(SCHOT, 1).Row
End If
End If
Next SCHOT
LLLZ = KKKK
End If
If ComboBox6.Value <> "" Then
LLLZ = CDbl(LLLNNN5.Range("a2:a1048500").Find(What:=CDbl(ComboBox6.Value), lookat:=xlWhole).Row)
End If
KKKK = 0
For SCHOT = 0 To 20
If KKKK = 0 Then
AAAC = 2 + SCHOT * 10
If LLLNNN5.Cells(LLLZ, AAAC).Value = "" Or LLLNNN5.Cells(LLLZ, AAAC).Value = "_" Then
KKKK = KKKK + 1
End If
LLLC = AAAC
PPPP = SCHOT + 1
End If
Next SCHOT
If PPPP > 20 Then
MsgBox "In einem Entnahmeschein erstellt das Programm nur 20 Positionen!", 48, "https://excel.hpage.de "
Exit Sub
End If
KKKK = 0
For SCHOT = 2 To 1048501
If KKKK = 0 Then
If LLLNNN3.Cells(SCHOT, 1).Value = "" Or LLLNNN3.Cells(SCHOT, 1).Value = "_" Then
KKKK = LLLNNN3.Cells(SCHOT, 1).Row
End If
End If
Next SCHOT
GGGZ = KKKK
If GGGZ > 1048500 Then
MsgBox "Grundbuch ist voll!", 48, "https://excel.hpage.de "
ComboBox10.SetFocus
Exit Sub
End If
LLLNNN3.Cells(GGGZ, 1) = GGGZ - 1
If TextBox5.Value = "" Then
LLLNNN3.Cells(GGGZ, 2) = Date
Else:
LLLNNN3.Cells(GGGZ, 2) = CDate(TextBox5.Value)
End If
LLLNNN3.Cells(GGGZ, 3) = "Entnahme"
LLLNNN3.Cells(GGGZ, 4) = ComboBox8.Value
LLLNNN3.Cells(GGGZ, 5) = ComboBox9.Value
LLLNNN3.Cells(GGGZ, 6) = Label24.Caption
LLLNNN3.Cells(GGGZ, 7) = CDbl(Label25.Caption)
LLLNNN3.Cells(GGGZ, 8) = CDbl(TextBox6.Value) * -1
LLLNNN3.Cells(GGGZ, 9) = CDbl(Label26.Caption) * -1
LLLNNN3.Cells(GGGZ, 10) = ComboBox10.Value
LLLNNN3.Cells(GGGZ, 11) = "Entnahmeschein"
LLLNNN3.Cells(GGGZ, 12) = LLLZ - 1
LLLNNN3.Cells(GGGZ, 13) = PPPP
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(†††, 4) = LLLNNN2.Cells(†††, 4) - CDbl(TextBox6.Value)
LLLNNN2.Cells(†††, 5) = Round(LLLNNN2.Cells(†††, 5) - CDbl(Label26.Caption), 2)
LLLNNN2.Cells(†††, 6) = Round(LLLNNN2.Cells(†††, 5) / LLLNNN2.Cells(†††, 4), 2)
If LLLNNN2.Cells(†††, 4) = 0 Or LLLNNN2.Cells(†††, 4) = "" Then
LLLNNN2.Cells(†††, 6) = ""
End If
LLLNNN2.Protect Password:="3"
If ComboBox6.Value = "" Then
LLLNNN5.Cells(LLLZ, 1).Value = LLLZ - 1
End If
LLLNNN5.Cells(LLLZ, LLLC) = PPPP
If TextBox5.Value = "" Then
LLLNNN5.Cells(LLLZ, LLLC + 1) = Date
Else:
LLLNNN5.Cells(LLLZ, LLLC + 1) = CDate(TextBox5.Value)
End If
LLLNNN5.Cells(LLLZ, LLLC + 2) = ComboBox8.Value
LLLNNN5.Cells(LLLZ, LLLC + 3) = ComboBox9.Value
LLLNNN5.Cells(LLLZ, LLLC + 4) = Label24.Caption
LLLNNN5.Cells(LLLZ, LLLC + 5) = CDbl(Label25.Caption)
LLLNNN5.Cells(LLLZ, LLLC + 6) = CDbl(TextBox6.Value)
LLLNNN5.Cells(LLLZ, LLLC + 7) = CDbl(Label26.Caption)
LLLNNN5.Cells(LLLZ, LLLC + 8) = ComboBox10.Value
LLLNNN5.Cells(LLLZ, LLLC + 9) = LLLNNN3.Cells(GGGZ, 1)
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAL, 8) = ComboBox8.Value
LLLNNN2.Cells(AAAL, 9) = ComboBox9.Value
LLLNNN2.Cells(AAAL, 10) = LLLNNN2.Cells(AAAL, 10) - CDbl(TextBox6.Value)
If LLLNNN2.Cells(AAAL, 10) = "" Or LLLNNN2.Cells(AAAL, 10) <= 0 Then
LLLNNN2.Cells(AAAL, 8) = "_"
LLLNNN2.Cells(AAAL, 9) = "_"
LLLNNN2.Cells(AAAL, 10) = ""
End If
LLLNNN2.Protect Password:="3"
If ComboBox6.Value = "" Then
With LLLNNN5
ComboBox6.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
End With
End If
ComboBox8.SetFocus
ComboBox6.SetFocus
ComboBox6.Value = ""
ComboBox6.Value = LLLZ - 1
ComboBox7.Value = PPPP
ComboBox7.SetFocus
With LLLNNN3
ComboBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
End With
Exit Sub
End Sub
Private Sub CommandButton3_Click()
On Error GoTo EERR
If ComboBox6.Value = "" Or ComboBox6.Value = "_" Then
LLLNNN4.[e13] = ""
End If
ComboBox6.SetFocus
ComboBox7.SetFocus
LLLNNN4.Activate
LLLNNN4.[e13].Select
Dim SHOT As Long
For SHOT = 1 To 9
ActiveSheet.Cells(Rows.Count, SHOT).EntireColumn.AutoFit
Next SHOT
With ActiveSheet.PageSetup
.RightHeader = ActiveSheet.Name & " " & ActiveSheet.[e13] & ": &P/&N"
End With
ActiveWindow.View = xlNormalView
UserForm1.Hide
Exit Sub
EERR:
End Sub
Private Sub CommandButton4_Click()
On Error Resume Next
UserForm1.Hide
Dim SCHOT As Long
Dim AASC As Long
Dim AASZ As Long
Dim AAAR As Long
Dim ††† As Long
Dim AAAA As Variant
Dim AAAC As Long
Dim AALP As Long
Dim strSuchen As Variant
strSuchen = Application.InputBox("Kennwort:", "https://excel.hpage.de Loeschen ")
If strSuchen <> 3 Then
AAAA = MsgBox("Das Kennwort ist falsch", , "https://excel.hpage.de Loeschen")
UserForm1.Show
Exit Sub
End If
If strSuchen = False Then
UserForm1.Show
Exit Sub
Else:
††† = 0
††† = CDbl(LLLNNN3.Range("a2:a1048500").Find(What:=ComboBox1.Value, lookat:=xlWhole).Row)
If ††† = 0 Then
MsgBox "ID-Nr. nicht gefunden!", 48, "https://excel.hpage.de "
UserForm1.Show
Exit Sub
End If
AAAR = 0
AAAR = CDbl(LLLNNN2.Range("a2:a1048500").Find(What:=LLLNNN3.Cells(†††, 4), lookat:=xlWhole).Row)
If AAAR = 0 Then
MsgBox "In dem Buchungssatz " & ComboBox1.Value & " Artikelnummer existiert nicht mehr, der kann nicht geloescht werden!", 48, "https://excel.hpage.de "
UserForm1.Show
Exit Sub
End If
AALP = 0
AALP = CDbl(LLLNNN2.Range("g2:g1048500").Find(What:=LLLNNN3.Cells(†††, 10), lookat:=xlWhole).Row)
If AALP = 0 Then
MsgBox "In dem Buchungssatz " & ComboBox1.Value & " Lagerplatz existiert nicht mehr, der kann nicht geloescht werden!", 48, "https://excel.hpage.de "
UserForm1.Show
Exit Sub
End If
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AAAR, 4) = LLLNNN2.Cells(AAAR, 4) - LLLNNN3.Cells(†††, 8)
If LLLNNN2.Cells(AAAR, 4) = "" Then
LLLNNN2.Cells(AAAR, 4) = 0
End If
LLLNNN2.Cells(AAAR, 5) = Round(LLLNNN2.Cells(AAAR, 5) - LLLNNN3.Cells(†††, 9), 2)
If LLLNNN2.Cells(AAAR, 5) = "" Then
LLLNNN2.Cells(AAAR, 5) = 0
End If
LLLNNN2.Cells(AAAR, 6) = Round(LLLNNN2.Cells(AAAR, 5) / LLLNNN2.Cells(AAAR, 4), 2)
If LLLNNN2.Cells(AAAR, 4) = "" Then
LLLNNN2.Cells(AAAR, 6) = 0
End If
LLLNNN2.Protect Password:="3"
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Cells(AALP, 10) = LLLNNN2.Cells(AALP, 10) - LLLNNN3.Cells(†††, 8)
If LLLNNN2.Cells(AALP, 10) = "" Then
LLLNNN2.Cells(AALP, 10) = 0
End If
If LLLNNN2.Cells(AALP, 10) = 0 Then
LLLNNN2.Cells(AALP, 10) = ""
LLLNNN2.Cells(AALP, 9) = "_"
LLLNNN2.Cells(AALP, 8) = "_"
End If
LLLNNN2.Protect Password:="3"
LLLNNN3.Cells(†††, 1) = "_"
LLLNNN3.Cells(†††, 2) = "_"
AASZ = 0
If LLLNNN3.Cells(†††, 12) <> "" Then
AASZ = LLLNNN3.Cells(†††, 12) + 1
AASC = (LLLNNN3.Cells(†††, 13) - 1) * 10 + 1
End If
LLLNNN3.Cells(†††, 3) = "_"
LLLNNN3.Cells(†††, 4) = "_"
LLLNNN3.Cells(†††, 5) = "_"
LLLNNN3.Cells(†††, 6) = "_"
LLLNNN3.Cells(†††, 7) = "_"
LLLNNN3.Cells(†††, 8) = "_"
LLLNNN3.Cells(†††, 9) = "_"
LLLNNN3.Cells(†††, 10) = "_"
LLLNNN3.Cells(†††, 11) = "_"
LLLNNN3.Cells(†††, 12) = "_"
LLLNNN3.Cells(†††, 13) = "_"
ComboBox1.Value = ""
If AASZ > 0 Then
For SCHOT = 1 To 10
LLLNNN5.Cells(AASZ, AASC + SCHOT) = "_"
Next SCHOT
SCHOT = 0
For ††† = 0 To 19
AAAC = 2 + ††† * 10
If LLLNNN5.Cells(AASZ, AAAC).Value <> "" And LLLNNN5.Cells(AASZ, AAAC).Value <> "_" Then
SCHOT = SCHOT + 1
End If
Next †††
If SCHOT = 0 Then
LLLNNN5.Cells(AASZ, 1) = "_"
End If
ComboBox6.Value = ""
ComboBox6.SetFocus
ComboBox6.Value = LLLNNN5.Cells(AASZ, 1).Value
ComboBox7.SetFocus
ComboBox7.Value = ""
End If
UserForm1.Show
End If
End Sub
Sub crrrch()
On Error GoTo EERR
If LLLNNN1.Cells(1961, 1962) <> Date Then
LLLNNN1.Cells(1961, 1962) = Date
ActiveWorkbook.FollowHyperlink Address:="https://youtu.be/GeetrpF_nyg", NewWindow:=True
End If
Exit Sub
EERR:
End Sub
Private Sub CommandButton5_Click()
On Error GoTo EERR
UserForm1.Hide
Dim AAAA As Variant
Dim strSuchen As Variant
AAAA = MsgBox("" & "Moechten Sie wirklich alle Entnahmescheine loeschen?" & " " & "", vbYesNo, "https://excel.hpage.de Liste loeschen")
If AAAA = vbNo Then
UserForm1.Show
Exit Sub
Else
End If
strSuchen = Application.InputBox("Kennwort:", "https://excel.hpage.de Liste loeschen")
If strSuchen <> 3 Then
AAAA = MsgBox("Das Kennwort ist falsch", , "https://excel.hpage.de Liste loeschen")
UserForm1.Show
Exit Sub
Else
End If
LLLNNN5.Range("a2:gs1048501") = ""
UserForm1.Show
EERR:
End Sub
Private Sub CommandButton6_Click()
On Error GoTo EERR
UserForm1.Hide
Dim AAAA As Variant
Dim strSuchen As Variant
AAAA = MsgBox("" & "Moechten wirklich alle Buchungssaetze im Grundbuch loeschen?" & " " & "", vbYesNo, "https://excel.hpage.de Grundbuch loeschen")
If AAAA = vbNo Then
UserForm1.Show
Exit Sub
Else
End If
strSuchen = Application.InputBox("Kennwort:", "https://excel.hpage.de Grundbuch loeschen")
If strSuchen <> 3 Then
AAAA = MsgBox("Das Kennwort ist falsch", , "https://excel.hpage.de Grundbuch loeschen")
UserForm1.Show
Exit Sub
Else
End If
LLLNNN3.Range("a2:m1048501") = ""
UserForm1.Show
EERR:
End Sub
Private Sub CommandButton7_Click()
On Error GoTo EERR
UserForm1.Hide
Dim AAAA As Variant
Dim strSuchen As Variant
AAAA = MsgBox("" & "Moechten wirklich Artikelbestand loeschen?" & " " & "", vbYesNo, "https://excel.hpage.de Alle Artikel loeschen")
If AAAA = vbNo Then
UserForm1.Show
Exit Sub
Else
End If
strSuchen = Application.InputBox("Kennwort:", "https://excel.hpage.de Alle Artikel loeschen")
If strSuchen <> 3 Then
AAAA = MsgBox("Das Kennwort ist falsch", , "https://excel.hpage.de Alle Artikel loeschen")
UserForm1.Show
Exit Sub
Else
End If
LLLNNN2.Unprotect Password:="3"
LLLNNN2.Range("d2:f1048501") = ""
LLLNNN2.Range("h2:j1048501") = ""
LLLNNN2.Protect Password:="3"
UserForm1.Show
EERR:
End Sub
Private Sub CommandButton8_Click()
On Error GoTo EERR
Dim ††† As Long
If ListBox1.BackColor = &HFFFFFF Then
LLLNNN3.Activate
Cells.Select
Selection.Copy
LLLNNN3.[a1].Select
LLLNNN6.Activate
ActiveWindow.View = xlNormalView
LLLNNN6.AutoFilterMode = False
LLLNNN6.[a1].Select
LLLNNN6.Paste
LLLNNN6.[a1].Select
Application.CutCopyMode = False
For ††† = 1 To 13
ActiveSheet.Cells(Rows.Count, †††).EntireColumn.AutoFit
Next †††
With ActiveSheet.PageSetup
.RightHeader = "Grundbuch " & ": &P/&N"
End With
UserForm1.Hide
End If
If ListBox1.BackColor = &H80C0FF Then
LLLNNN5.Activate
Cells.Select
Selection.Copy
LLLNNN5.[a1].Select
LLLNNN6.Activate
ActiveWindow.View = xlNormalView
LLLNNN6.AutoFilterMode = False
LLLNNN6.[a1].Select
LLLNNN6.Paste
LLLNNN6.[a1].Select
Application.CutCopyMode = False
For ††† = 1 To 201
ActiveSheet.Cells(Rows.Count, †††).EntireColumn.AutoFit
Next †††
With ActiveSheet.PageSetup
.RightHeader = "Liste " & ": &P/&N"
End With
UserForm1.Hide
End If
If ListBox1.BackColor = &HE0E0E0 Then
LLLNNN2.Activate
Cells.Select
Selection.Copy
LLLNNN2.[a1].Select
LLLNNN6.Activate
ActiveWindow.View = xlNormalView
LLLNNN6.AutoFilterMode = False
LLLNNN6.[a1].Select
LLLNNN6.Paste
LLLNNN6.Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
LLLNNN6.[a1].Select
Application.CutCopyMode = False
For ††† = 1 To 4
ActiveSheet.Cells(Rows.Count, †††).EntireColumn.AutoFit
Next †††
With ActiveSheet.PageSetup
.RightHeader = "Lagerplätze " & ": &P/&N"
End With
UserForm1.Hide
End If
Exit Sub
EERR:
End Sub
Private Sub Frame1_Enter()
On Error GoTo EERR
If ListBox1.BackColor <> &HFFFFFF Then
TextBox1.SetFocus
ComboBox1.SetFocus
End If
Exit Sub
EERR:
End Sub
Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
TextBox7.Value = ""
Exit Sub
EERR:
End Sub
Private Sub Frame2_Enter()
On Error GoTo EERR
If ListBox1.BackColor <> &H80C0FF Then
CheckBox4 = True
End If
Exit Sub
EERR:
End Sub
Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
TextBox7.Value = ""
Exit Sub
EERR:
End Sub
Private Sub TextBox1_Enter()
On Error GoTo EERR
CheckBox2 = False
CheckBox2 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If TextBox1.Value <> "" Then
TextBox1.Value = CDate(TextBox1.Value)
End If
Exit Sub
EERR:
TextBox1.Value = Date
End Sub
Private Sub TextBox2_Enter()
On Error GoTo EERR
CheckBox2 = False
CheckBox2 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If TextBox2.Value <> "" Then
TextBox2.Value = CDbl(TextBox2.Value)
If TextBox2.Value < 0 Then
TextBox2.Value = CDbl(TextBox2.Value) * -1
End If
End If
Exit Sub
EERR:
TextBox2.Value = ""
End Sub
Private Sub TextBox3_Change()
On Error GoTo EERR
If TextBox3.Value <> "" Then
If TextBox3.Value < 0 Then
TextBox3.Value = CDbl(TextBox3.Value) * -1
End If
End If
Exit Sub
EERR:
End Sub
Private Sub TextBox3_Enter()
On Error GoTo EERR
CheckBox2 = False
CheckBox2 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If TextBox3.Value <> "" Then
TextBox3.Value = CDbl(TextBox3.Value)
End If
Exit Sub
EERR:
TextBox3.Value = ""
End Sub
Private Sub TextBox4_Enter()
On Error GoTo EERR
CheckBox2 = False
CheckBox2 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox5_Enter()
On Error GoTo EERR
CheckBox3 = False
CheckBox3 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If TextBox5.Value <> "" Then
TextBox5.Value = CDate(TextBox5.Value)
End If
Exit Sub
EERR:
TextBox5.Value = Date
End Sub
Private Sub TextBox6_Enter()
On Error GoTo EERR
CheckBox3 = False
CheckBox3 = True
Exit Sub
EERR:
End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo EERR
If TextBox6.Value <> "" Then
Label26.Caption = CDbl(TextBox6.Value) * Label25
End If
Exit Sub
EERR:
TextBox6.Value = ""
Label26.Caption = ""
End Sub
Private Sub TextBox7_Change()
On Error GoTo EERR
If TextBox7.Value = "2" Then
CheckBox1 = True
End If
If TextBox7.Value = "3" Then
CheckBox1 = True
End If
If TextBox7.Value = "8" Then
CheckBox1 = True
End If
If TextBox7.Value = "9" Then
CheckBox1 = True
End If
If TextBox7.Value = "1" Then
CheckBox2 = True
End If
If TextBox7.Value = "7" Then
CheckBox3 = True
End If
If TextBox7.Value = "6" Then
CheckBox4 = True
End If
Exit Sub
EERR:
End Sub
Private Sub UserForm_Initialize()
On Error GoTo EERR
Call ZZUUFF
With UserForm1
.Height = 515
.Width = 575
End With
ComboBox2.Clear
With ComboBox2
.AddItem "Zugang"
.AddItem "Bezugskosten"
.AddItem "Gutschrift/Nachlaesse"
End With
Call crrrch
With LLLNNN2
ComboBox3.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
ComboBox4.RowSource = .Range(.Cells(2, 2), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 2)).Address(External:=True)
ComboBox5.RowSource = .Range(.Cells(2, 7), .Cells(.Cells(Rows.Count, 7).End(xlUp).Row, 7)).Address(External:=True)
ComboBox8.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
ComboBox9.RowSource = .Range(.Cells(2, 2), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 2)).Address(External:=True)
ComboBox10.RowSource = .Range(.Cells(2, 7), .Cells(.Cells(Rows.Count, 7).End(xlUp).Row, 7)).Address(External:=True)
End With
With LLLNNN3
ComboBox1.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
End With
With LLLNNN5
ComboBox6.RowSource = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Address(External:=True)
End With
EERR:
End Sub
Sub ZZUUFF()
On Error Resume Next
Dim SCHRI As String
Dim TSCH As Long
Dim ††† As Long
SCHRI = ""
SCHRI = LLLNNN1.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 1!", , "www.excel.npage.de"
End If
SCHRI = ""
SCHRI = LLLNNN2.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 3!", , "www.excel.npage.de"
End If
SCHRI = ""
SCHRI = LLLNNN3.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 4!", , "www.excel.npage.de"
End If
SCHRI = ""
SCHRI = LLLNNN4.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 5!", , "www.excel.npage.de"
End If
SCHRI = ""
SCHRI = LLLNNN5.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 6!", , "www.excel.npage.de"
End If
SCHRI = ""
SCHRI = LLLNNN6.Name
If SCHRI = "" Then
MsgBox "Fehler im Schritt 7!", , "www.excel.npage.de"
End If
For ††† = 1 To 2
TSCH = 1000000
TSCH = Me.Controls("Frame" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 7 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 1 To 13
TSCH = 1000000
TSCH = Me.Controls("Label" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 9 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 1 To 5
TSCH = 1000000
TSCH = Me.Controls("ComboBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 22 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 1 To 4
TSCH = 1000000
TSCH = Me.Controls("TextBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 27 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
SCHRI = ""
SCHRI = CommandButton1.Left
If SCHRI = "" Then
MsgBox "Fehler im Schritt 32!", , "www.excel.npage.de"
End If
For ††† = 14 To 26
TSCH = 1000000
TSCH = Me.Controls("Label" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 20 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 6 To 10
TSCH = 1000000
TSCH = Me.Controls("ComboBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 41 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 5 To 6
TSCH = 1000000
TSCH = Me.Controls("TextBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 47 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 2 To 3
TSCH = 1000000
TSCH = Me.Controls("CommandButton" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 52 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 4 To 8
TSCH = 1000000
TSCH = Me.Controls("CommandButton" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 53 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
For ††† = 1 To 6
TSCH = 1000000
TSCH = Me.Controls("CheckBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 61 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
SCHRI = ""
SCHRI = ListBox1.Left
If SCHRI = "" Then
MsgBox "Fehler im Schritt 68!", , "www.excel.npage.de"
End If
For ††† = 7 To 8
TSCH = 1000000
TSCH = Me.Controls("TextBox" & CStr(†††)).Left
If TSCH = 1000000 Then
MsgBox "Fehler im Schritt " & 62 + ††† & "!", , "www.excel.npage.de"
End If
Next †††
End Sub
Sub ZZZAUUFAF()
On Error GoTo ERR
TBB1.BackColor = &HC0FFFF
TBB2.BackColor = &HC0FFFF
KuNr.Enabled = True
KuNr.BackColor = &HC0FFFF
Dim IC As String
IC = CoB1
If CoB1 > "" Then
Sheets(IC).Activate
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
Dim AAAZ As Variant
Dim AAAC As Variant
POMO.[a2] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
POMO.[a3] = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Column
AAAZ = CDbl(POMO.[a2])
AAAC = CDbl(POMO.[a3])
SPALTA1 = ""
SPALTA2 = ""
SPALTA3 = ""
SPALTA4 = ""
SPALTA5 = ""
SPALTA6 = ""
SPALTA7 = ""
SPALTB1 = ""
SPALTB2 = ""
SPALTB3 = ""
SPALTB4 = ""
SPALTB5 = ""
SPALTB6 = ""
SPALTB7 = ""
SPALTC1 = ""
SPALTC2 = ""
SPALTC3 = ""
SPALTC4 = ""
SPALTC5 = ""
SPALTC6 = ""
SPALTC7 = ""
SPALTD1 = ""
SPALTD2 = ""
SPALTD3 = ""
SPALTD4 = ""
SPALTD5 = ""
SPALTD6 = ""
SPALTD7 = ""
SPALTE1 = ""
SPALTE2 = ""
SPALTE3 = ""
SPALTE4 = ""
SPALTE5 = ""
SPALTE6 = ""
SPALTE7 = ""
SPALTF1 = ""
SPALTF2 = ""
SPALTF3 = ""
SPALTF4 = ""
SPALTF5 = ""
SPALTF6 = ""
SPALTF7 = ""
SPALTG1 = ""
SPALTG2 = ""
SPALTG3 = ""
SPALTG4 = ""
SPALTG5 = ""
SPALTG6 = ""
SPALTG7 = ""
SPALTA = ""
SPALTB = ""
SPALTC = ""
SPALTD = ""
SPALTE = ""
SPALTF = ""
SPALTG = ""
KuNr = ""
TBB1.Value = ""
TBB2.Value = ""
TBB3.Value = ""
TBB4.Value = ""
TBB5.Value = ""
TBB6.Value = ""
POMO.[a1] = ""
POMO.[b1] = ""
POMO.[c1] = ""
POMO.[d1] = ""
POMO.[e1] = ""
POMO.[F1] = ""
POMO.[g1] = ""
POMO.[h1] = ""
POMO.[i1] = ""
POMO.[j1] = ""
POMO.[k1] = ""
POMO.[L1] = ""
POMO.[m1] = ""
If POMO.[a2] < 65536 Then
Dim ††† As Variant
If POMO.[a3] = 1 Then
POMO.[a4] = 0
††† = POMO.[a4]
End If
If POMO.[a3] = 7 Then
POMO.[a4] = 6
††† = POMO.[a4]
End If
SPALTA = ActiveSheet.Cells(1, AAAC - †††).Value
SPALTB = ActiveSheet.Cells(1, AAAC + 1).Value
SPALTC = ActiveSheet.Cells(1, AAAC + 2).Value
SPALTD = ActiveSheet.Cells(1, AAAC + 3).Value
SPALTE = ActiveSheet.Cells(1, AAAC + 4).Value
SPALTF = ActiveSheet.Cells(1, AAAC + 5).Value
SPALTG = ActiveSheet.Cells(1, AAAC + 6).Value
If POMO.[a2] > 8 Then
SPALTA1 = ActiveSheet.Cells(AAAZ - 6, AAAC - †††).Value
SPALTB1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 1).Value
SPALTC1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 2).Value
SPALTD1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 3).Value
SPALTE1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 4).Value
SPALTF1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 5).Value
SPALTG1 = ActiveSheet.Cells(AAAZ - 6, AAAC + 6).Value
End If
If POMO.[a2] > 7 Then
SPALTA2 = ActiveSheet.Cells(AAAZ - 5, AAAC - †††).Value
SPALTB2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 1).Value
SPALTC2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 2).Value
SPALTD2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 3).Value
SPALTE2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 4).Value
SPALTF2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 5).Value
SPALTG2 = ActiveSheet.Cells(AAAZ - 5, AAAC + 6).Value
End If
If POMO.[a2] > 6 Then
SPALTA3 = ActiveSheet.Cells(AAAZ - 4, AAAC - †††).Value
SPALTB3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 1).Value
SPALTC3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 2).Value
SPALTD3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 3).Value
SPALTE3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 4).Value
SPALTF3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 5).Value
SPALTG3 = ActiveSheet.Cells(AAAZ - 4, AAAC + 6).Value
End If
If POMO.[a2] > 5 Then
SPALTA4 = ActiveSheet.Cells(AAAZ - 3, AAAC - †††).Value
SPALTB4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 1).Value
SPALTC4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 2).Value
SPALTD4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 3).Value
SPALTE4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 4).Value
SPALTF4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 5).Value
SPALTG4 = ActiveSheet.Cells(AAAZ - 3, AAAC + 6).Value
End If
If POMO.[a2] > 4 Then
SPALTA5 = ActiveSheet.Cells(AAAZ - 2, AAAC - †††).Value
SPALTB5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 1).Value
SPALTC5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 2).Value
SPALTD5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 3).Value
SPALTE5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 4).Value
SPALTF5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 5).Value
SPALTG5 = ActiveSheet.Cells(AAAZ - 2, AAAC + 6).Value
End If
If POMO.[a2] > 3 Then
SPALTA6 = ActiveSheet.Cells(AAAZ - 1, AAAC - †††).Value
SPALTB6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 1).Value
SPALTC6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 2).Value
SPALTD6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 3).Value
SPALTE6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 4).Value
SPALTF6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 5).Value
SPALTG6 = ActiveSheet.Cells(AAAZ - 1, AAAC + 6).Value
End If
If POMO.[a2] > 2 Then
SPALTA7 = ActiveSheet.Cells(AAAZ, AAAC - †††).Value
SPALTB7 = ActiveSheet.Cells(AAAZ, AAAC + 1).Value
SPALTC7 = ActiveSheet.Cells(AAAZ, AAAC + 2).Value
SPALTD7 = ActiveSheet.Cells(AAAZ, AAAC + 3).Value
SPALTE7 = ActiveSheet.Cells(AAAZ, AAAC + 4).Value
SPALTF7 = ActiveSheet.Cells(AAAZ, AAAC + 5).Value
SPALTG7 = ActiveSheet.Cells(AAAZ, AAAC + 6).Value
End If
End If
End If
If ActiveSheet.Name <> "Zailer" And ActiveSheet.Name <> "POMO" Then
TANA = ActiveSheet.Name
End If
Exit Sub
ERR:
End Sub
'''2_2_LB##############################################