Hallo Yummi,
nachfolgend der von mir erstellte Code!
Würd mich freuen wenn du damit was anfangen kannst, bzw eine Lösung für mein Problem findest.
Gruß Linus
Option Explicit
Public klick As Long
Public schalter As Boolean
'Datenbank aufrufen
Private Sub Label14_Click()
Unload Me
Application.Visible = True
End Sub
'Grundeinstellungen
Private Sub UserForm_Initialize()
SpalteA
SpalteC
SpalteD
SpalteE
ComboBox4 = ""
CommandButton6.ForeColor = vbRed
Label19.Top = 282
'Anzeige Abteilung
Label13.Caption = "FFZ - PZL " & Sheets("FFZ").Cells(1, 1)
Label18.Caption = "Kostenstelle: " & Sheets("FFZ").Cells(2, 1)
'Anzeige Anzahl Fahrzeuge
Label17.Caption = "In dieser Abteilung sind " & Sheets("FFZ").Cells(1, 16) & _
" Fahrzeuge registriert."
'UVV Label verbergen
Label19.Visible = False
'Formate
Größe_Anfang
'Seitensichtarkeiten
FFZ.MultiPage1.Value = 0
MultiPage1.Pages(0).Enabled = 1
MultiPage1.Pages(1).Enabled = 0
'Item Auswahl für ComboBoxen
ComboBox1.RowSource = "Daten!A2:A8"
ComboBox2.RowSource = "Daten!B2:B8"
ComboBox3.RowSource = "Daten!D2:D8"
ComboBox4.RowSource = "Daten!E2:E8"
'ListBox
Listbox_Verwalten
'Sichbarkeiten
CommandButton6.Visible = False
Label15.Visible = False
ComboBox6.Visible = False
Label16.Visible = False
End Sub
'Sichtbarkeit der Auswahl an wen das Fahrzeug verliehen wurde
Private Sub ComboBox3_Change()
If ComboBox3 = "Ausgeliehen" Then
ComboBox6.Visible = True
Label16.Visible = True
ComboBox6.RowSource = "Daten!F2:F8"
Else
ComboBox6.Visible = False
ComboBox6 = ""
End If
End Sub
'Änderung der Verwendung des FFZ
Private Sub ComboBox4_Change()
If ComboBox4 = "Einsatz" Then
ComboBox5.Enabled = False
DTPicker3.Enabled = False
DTPicker3.Value = Empty
ComboBox5 = ""
End If
If ComboBox4 = "Verliehen" Then
ComboBox5.Enabled = True
ComboBox5 = ""
ComboBox5.RowSource = "Daten!F2:F10"
DTPicker3.Enabled = True
End If
If ComboBox4 = "Reserve" Then
ComboBox5.Enabled = False
ComboBox5 = ""
DTPicker3.Enabled = False
DTPicker3.Value = Empty
End If
If ComboBox4 = "Werkstatt" Then
ComboBox5 = "Werkstatt"
ComboBox5.Enabled = False
DTPicker3.Enabled = True
End If
If ComboBox4 = "Verschrottet" Then
ComboBox5 = "Verschrottet"
ComboBox5.Enabled = False
DTPicker3.Enabled = True
End If
If ComboBox4 = "Vergeben" Then
ComboBox5.Enabled = True
ComboBox5 = ""
ComboBox5.RowSource = "Daten!F2:F10"
DTPicker3.Enabled = True
End If
If ComboBox4 = "Leasingrückgabe" Then
ComboBox5 = "Leasingrückgabe"
ComboBox5.Enabled = False
DTPicker3.Enabled = True
End If
End Sub
'X-Button oben rechts deaktivieren
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode 1 Then
Cancel = 1
End If
End Sub
'ListBox
Private Sub Listbox_Verwalten()
Dim loletzteX As Long
loletzteX = Sheets("FFZ").Cells(Rows.Count, 1).End(xlUp).Row
With ListBox1
.ColumnCount = 12
.ColumnWidths = "2cm;1,5cm;2,5cm;2,5cm;2cm;2,2cm;2,5cm;2,5cm;2,5cm;2, _
2cm;2,5cm;5cm"
.ColumnHeads = True
ListBox1.RowSource = "FFZ!A10:HH" & loletzteX
End With
ListBox1.ListIndex = ListBox1.ListCount - 1
End Sub
'FZ erfassen - Teil 1
'Auswahl der Fahrzeugerfassung und Sprung zur Multipage 2
Private Sub CommandButton3_Click()
MultiPage1.Pages(0).Enabled = 0
MultiPage1.Pages(1).Enabled = 1
FFZ.MultiPage1.Value = 1
CommandButton4.Visible = False
CommandButton1.Visible = True
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
DTPicker1 = Date
DTPicker2 = Date
DTPicker3 = Date
DTPicker3.Value = Empty
DTPicker3.Enabled = False
ComboBox5.Enabled = False
ComboBox6.Visible = False
Label16.Visible = False
Größe_Ende
End Sub
'FZ erfassen - Teil 2
'Neuerfassung des Fahrzeuges
Private Sub CommandButton1_Click()
If DTPicker3.Value "" And ComboBox5 = "" Then
MsgBox "Bitte geben Sie an, wohin das Fahrzeug gegangen ist!"
Else
If TextBox2 = "" Or ComboBox1 = "" Or ComboBox2 = "" Then
MsgBox "Bitte pflegen Sie die Fahrzeugdaten ordnungsgemäß ein!"
Else
Dim erste_freie_Zeile As Integer
erste_freie_Zeile = Sheets("FFZ").Range("B65536").End(xlUp).Offset(1, 0). _
Row
Sheets("FFZ").Cells(erste_freie_Zeile, 2) = TextBox2 'Ext.Nr.
Sheets("FFZ").Cells(erste_freie_Zeile, 3) = ComboBox1 'Fahrzeugart
Sheets("FFZ").Cells(erste_freie_Zeile, 4) = ComboBox2 'Hersteller
TextBox1 = Sheets("FFZ").Cells(erste_freie_Zeile, 15)
Sheets("FFZ").Cells(erste_freie_Zeile, 1) = TextBox1 'Int.Nr.
Sheets("FFZ").Cells(erste_freie_Zeile, 5) = DTPicker1 'UVV
Sheets("FFZ").Cells(erste_freie_Zeile, 6) = DTPicker2 'Erhalten am:
Sheets("FFZ").Cells(erste_freie_Zeile, 7) = ComboBox3 'Herkunft
Sheets("FFZ").Cells(erste_freie_Zeile, 8) = ComboBox6 'Herkunft
Sheets("FFZ").Cells(erste_freie_Zeile, 9) = ComboBox4 'Verwendung
Sheets("FFZ").Cells(erste_freie_Zeile, 10) = DTPicker3 'Abegegebn am:
Sheets("FFZ").Cells(erste_freie_Zeile, 11) = ComboBox5 'Abegegebn an:
Sheets("FFZ").Cells(erste_freie_Zeile, 12) = TextBox3 'Bemerkung
MultiPage1.Pages(0).Enabled = 1
MultiPage1.Pages(1).Enabled = 0
FFZ.MultiPage1.Value = 0
Listbox_Verwalten
Größe_Anfang
End If
'Anzeige Anzahl Fahrzeuge
Label17.Caption = "In dieser Abteilung sind " & Sheets("FFZ").Cells(1, 16) & _
" Fahrzeuge registriert."
End If
SpalteA
SpalteC
SpalteD
SpalteE
End Sub
'Fahrzeug bearbeiten - Teil 1
'Auswahl der Fahrzeugbearbeitung und Sprung zur Multipage 2
Private Sub CommandButton2_Click()
MultiPage1.Pages(0).Enabled = 0
MultiPage1.Pages(1).Enabled = 1
FFZ.MultiPage1.Value = 1
CommandButton4.Visible = True
CommandButton1.Visible = False
With ListBox1
FFZ.TextBox1 = .List(.ListIndex, 14)
FFZ.TextBox2 = .List(.ListIndex, 1)
FFZ.ComboBox1 = .List(.ListIndex, 2)
FFZ.ComboBox2 = .List(.ListIndex, 3)
FFZ.DTPicker1 = .List(.ListIndex, 4)
FFZ.DTPicker2 = .List(.ListIndex, 5)
FFZ.ComboBox3 = .List(.ListIndex, 6)
FFZ.ComboBox6 = .List(.ListIndex, 7)
FFZ.ComboBox4 = .List(.ListIndex, 8)
FFZ.DTPicker3 = .List(.ListIndex, 9)
FFZ.ComboBox5 = .List(.ListIndex, 10)
FFZ.TextBox3 = .List(.ListIndex, 11)
End With
Größe_Ende
'DTPicker3.Value = Empty
'DTPicker3.Enabled = False
'ComboBox5.Enabled = False
If ComboBox3 = "Ausgeliehen" Then
ComboBox6.Visible = True
Label16.Visible = True
Else
ComboBox6.Visible = False
Label16.Visible = False
End If
End Sub
'Fahrzeug bearbeiten - Teil 2
'Änderungen am Fahrzeug speichern
Private Sub CommandButton4_Click()
If DTPicker3.Value "" And ComboBox5 = "" Then
MsgBox "Bitte geben Sie an, wohin das Fahrzeug gegangen ist!"
Else
Bearbeitung_speichern
End If
SpalteA
SpalteC
SpalteD
SpalteE
End Sub
Private Sub Bearbeitung_speichern()
Dim erste_freie_Zeile As Integer
erste_freie_Zeile = Sheets("FFZ").Range("B65536").End(xlUp).Offset(1, 0). _
Row
With Range("FFZ!A10:Z100")
FFZ.Tag = "1"
'.Cells(FFZ.ListBox1.ListIndex + 1, 1).Value = FFZ.TextBox1
.Cells(FFZ.ListBox1.ListIndex + 1, 2).Value = CDbl(FFZ.TextBox2)
.Cells(FFZ.ListBox1.ListIndex + 1, 3).Value = FFZ.ComboBox1
.Cells(FFZ.ListBox1.ListIndex + 1, 4).Value = FFZ.ComboBox2
.Cells(FFZ.ListBox1.ListIndex + 1, 5).Value = FFZ.DTPicker1
.Cells(FFZ.ListBox1.ListIndex + 1, 6).Value = FFZ.DTPicker2
.Cells(FFZ.ListBox1.ListIndex + 1, 7).Value = FFZ.ComboBox3
.Cells(FFZ.ListBox1.ListIndex + 1, 8).Value = FFZ.ComboBox6
.Cells(FFZ.ListBox1.ListIndex + 1, 9).Value = FFZ.ComboBox4
.Cells(FFZ.ListBox1.ListIndex + 1, 10).Value = FFZ.DTPicker3
.Cells(FFZ.ListBox1.ListIndex + 1, 11).Value = FFZ.ComboBox5
.Cells(FFZ.ListBox1.ListIndex + 1, 12).Value = FFZ.TextBox3
FFZ.Tag = ""
End With
TextBox1 = ListBox1.List(ListBox1.ListIndex, 14)
With Range("FFZ!A10:Z100")
FFZ.Tag = "1"
.Cells(FFZ.ListBox1.ListIndex + 1, 1).Value = FFZ.TextBox1
FFZ.Tag = ""
End With
FFZ.MultiPage1.Value = 0
MultiPage1.Pages(0).Enabled = 1
MultiPage1.Pages(1).Enabled = 0
Größe_Anfang
End Sub
' Auswirkungen bei anklicken der Listbox
Private Sub ListBox1_Click()
' Anzeigen der Bedingungen zum Löschen von Fahrzeugen
If ListBox1.List(ListBox1.ListIndex, 9) 0
If arrSelected(i) Then Exit For
Next
Next
With ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = arrSelected(i)
Next
End With
End Sub
'Auditliste aktuell halten
'Auditliste aktuell halten - Spalte A - Int. Nummer
Private Sub SpalteA()
If Sheets("FFZ").Cells(10, 1) = "" Then
Sheets("Auditliste").Cells(2, 1) = ""
Else
Sheets("Auditliste").Cells(2, 1) = "=FFZ!A10"
End If
If Sheets("FFZ").Cells(11, 1) = "" Then
Sheets("Auditliste").Cells(3, 1) = ""
Else
Sheets("Auditliste").Cells(3, 1) = "=FFZ!A11"
End If
If Sheets("FFZ").Cells(12, 1) = "" Then
Sheets("Auditliste").Cells(4, 1) = ""
Else
Sheets("Auditliste").Cells(4, 1) = "=FFZ!A12"
End If
If Sheets("FFZ").Cells(13, 1) = "" Then
Sheets("Auditliste").Cells(5, 1) = ""
Else
Sheets("Auditliste").Cells(5, 1) = "=FFZ!A13"
End If
If Sheets("FFZ").Cells(14, 1) = "" Then
Sheets("Auditliste").Cells(6, 1) = ""
Else
Sheets("Auditliste").Cells(6, 1) = "=FFZ!A14"
End If
If Sheets("FFZ").Cells(15, 1) = "" Then
Sheets("Auditliste").Cells(7, 1) = ""
Else
Sheets("Auditliste").Cells(7, 1) = "=FFZ!A15"
End If
If Sheets("FFZ").Cells(16, 1) = "" Then
Sheets("Auditliste").Cells(8, 1) = ""
Else
Sheets("Auditliste").Cells(8, 1) = "=FFZ!A16"
End If
If Sheets("FFZ").Cells(17, 1) = "" Then
Sheets("Auditliste").Cells(9, 1) = ""
Else
Sheets("Auditliste").Cells(9, 1) = "=FFZ!A17"
End If
If Sheets("FFZ").Cells(18, 1) = "" Then
Sheets("Auditliste").Cells(10, 1) = ""
Else
Sheets("Auditliste").Cells(10, 1) = "=FFZ!A18"
End If
If Sheets("FFZ").Cells(19, 1) = "" Then
Sheets("Auditliste").Cells(11, 1) = ""
Else
Sheets("Auditliste").Cells(11, 1) = "=FFZ!A19"
End If
If Sheets("FFZ").Cells(20, 1) = "" Then
Sheets("Auditliste").Cells(12, 1) = ""
Else
Sheets("Auditliste").Cells(12, 1) = "=FFZ!A20"
End If
If Sheets("FFZ").Cells(21, 1) = "" Then
Sheets("Auditliste").Cells(13, 1) = ""
Else
Sheets("Auditliste").Cells(13, 1) = "=FFZ!A21"
End If
If Sheets("FFZ").Cells(22, 1) = "" Then
Sheets("Auditliste").Cells(14, 1) = ""
Else
Sheets("Auditliste").Cells(14, 1) = "=FFZ!A22"
End If
If Sheets("FFZ").Cells(23, 1) = "" Then
Sheets("Auditliste").Cells(15, 1) = ""
Else
Sheets("Auditliste").Cells(15, 1) = "=FFZ!A23"
End If
If Sheets("FFZ").Cells(24, 1) = "" Then
Sheets("Auditliste").Cells(16, 1) = ""
Else
Sheets("Auditliste").Cells(16, 1) = "=FFZ!A24"
End If
If Sheets("FFZ").Cells(25, 1) = "" Then
Sheets("Auditliste").Cells(17, 1) = ""
Else
Sheets("Auditliste").Cells(17, 1) = "=FFZ!A25"
End If
If Sheets("FFZ").Cells(26, 1) = "" Then
Sheets("Auditliste").Cells(18, 1) = ""
Else
Sheets("Auditliste").Cells(18, 1) = "=FFZ!A26"
End If
If Sheets("FFZ").Cells(27, 1) = "" Then
Sheets("Auditliste").Cells(19, 1) = ""
Else
Sheets("Auditliste").Cells(19, 1) = "=FFZ!A27"
End If
If Sheets("FFZ").Cells(28, 1) = "" Then
Sheets("Auditliste").Cells(20, 1) = ""
Else
Sheets("Auditliste").Cells(20, 1) = "=FFZ!A28"
End If
If Sheets("FFZ").Cells(29, 1) = "" Then
Sheets("Auditliste").Cells(21, 1) = ""
Else
Sheets("Auditliste").Cells(21, 1) = "=FFZ!A29"
End If
If Sheets("FFZ").Cells(30, 1) = "" Then
Sheets("Auditliste").Cells(22, 1) = ""
Else
Sheets("Auditliste").Cells(22, 1) = "=FFZ!A30"
End If
End Sub
'Auditliste aktuell halten - Spalte C - TYP
Private Sub SpalteC()
If Sheets("FFZ").Cells(10, 1) = "" Then
Sheets("Auditliste").Cells(2, 2) = ""
Else
Sheets("Auditliste").Cells(2, 2) = "=FFZ!C10"
End If
If Sheets("FFZ").Cells(11, 1) = "" Then
Sheets("Auditliste").Cells(3, 2) = ""
Else
Sheets("Auditliste").Cells(3, 2) = "=FFZ!C11"
End If
If Sheets("FFZ").Cells(12, 1) = "" Then
Sheets("Auditliste").Cells(4, 2) = ""
Else
Sheets("Auditliste").Cells(4, 2) = "=FFZ!C12"
End If
If Sheets("FFZ").Cells(13, 1) = "" Then
Sheets("Auditliste").Cells(5, 2) = ""
Else
Sheets("Auditliste").Cells(5, 2) = "=FFZ!C13"
End If
If Sheets("FFZ").Cells(14, 1) = "" Then
Sheets("Auditliste").Cells(6, 2) = ""
Else
Sheets("Auditliste").Cells(6, 2) = "=FFZ!C14"
End If
If Sheets("FFZ").Cells(15, 1) = "" Then
Sheets("Auditliste").Cells(7, 2) = ""
Else
Sheets("Auditliste").Cells(7, 2) = "=FFZ!C15"
End If
If Sheets("FFZ").Cells(16, 1) = "" Then
Sheets("Auditliste").Cells(8, 2) = ""
Else
Sheets("Auditliste").Cells(8, 2) = "=FFZ!C16"
End If
If Sheets("FFZ").Cells(17, 1) = "" Then
Sheets("Auditliste").Cells(9, 2) = ""
Else
Sheets("Auditliste").Cells(9, 2) = "=FFZ!C17"
End If
If Sheets("FFZ").Cells(18, 1) = "" Then
Sheets("Auditliste").Cells(10, 2) = ""
Else
Sheets("Auditliste").Cells(10, 2) = "=FFZ!C18"
End If
If Sheets("FFZ").Cells(19, 1) = "" Then
Sheets("Auditliste").Cells(11, 2) = ""
Else
Sheets("Auditliste").Cells(11, 2) = "=FFZ!C19"
End If
If Sheets("FFZ").Cells(20, 1) = "" Then
Sheets("Auditliste").Cells(12, 2) = ""
Else
Sheets("Auditliste").Cells(12, 2) = "=FFZ!C20"
End If
If Sheets("FFZ").Cells(21, 1) = "" Then
Sheets("Auditliste").Cells(13, 2) = ""
Else
Sheets("Auditliste").Cells(13, 2) = "=FFZ!C21"
End If
If Sheets("FFZ").Cells(22, 1) = "" Then
Sheets("Auditliste").Cells(14, 2) = ""
Else
Sheets("Auditliste").Cells(14, 2) = "=FFZ!C22"
End If
If Sheets("FFZ").Cells(23, 1) = "" Then
Sheets("Auditliste").Cells(15, 2) = ""
Else
Sheets("Auditliste").Cells(15, 2) = "=FFZ!C23"
End If
If Sheets("FFZ").Cells(24, 1) = "" Then
Sheets("Auditliste").Cells(16, 2) = ""
Else
Sheets("Auditliste").Cells(16, 2) = "=FFZ!C24"
End If
If Sheets("FFZ").Cells(25, 1) = "" Then
Sheets("Auditliste").Cells(17, 2) = ""
Else
Sheets("Auditliste").Cells(17, 2) = "=FFZ!C25"
End If
If Sheets("FFZ").Cells(26, 1) = "" Then
Sheets("Auditliste").Cells(18, 2) = ""
Else
Sheets("Auditliste").Cells(18, 2) = "=FFZ!C26"
End If
If Sheets("FFZ").Cells(27, 1) = "" Then
Sheets("Auditliste").Cells(19, 2) = ""
Else
Sheets("Auditliste").Cells(19, 2) = "=FFZ!C27"
End If
If Sheets("FFZ").Cells(28, 1) = "" Then
Sheets("Auditliste").Cells(20, 2) = ""
Else
Sheets("Auditliste").Cells(20, 2) = "=FFZ!C28"
End If
If Sheets("FFZ").Cells(29, 1) = "" Then
Sheets("Auditliste").Cells(21, 2) = ""
Else
Sheets("Auditliste").Cells(21, 2) = "=FFZ!C29"
End If
If Sheets("FFZ").Cells(30, 1) = "" Then
Sheets("Auditliste").Cells(22, 2) = ""
Else
Sheets("Auditliste").Cells(22, 2) = "=FFZ!C30"
End If
End Sub
'Auditliste aktuell halten - Spalte D - Hersteller
Private Sub SpalteD()
If Sheets("FFZ").Cells(10, 1) = "" Then
Sheets("Auditliste").Cells(2, 3) = ""
Else
Sheets("Auditliste").Cells(2, 3) = "=FFZ!D10"
End If
If Sheets("FFZ").Cells(11, 1) = "" Then
Sheets("Auditliste").Cells(3, 3) = ""
Else
Sheets("Auditliste").Cells(3, 3) = "=FFZ!D11"
End If
If Sheets("FFZ").Cells(12, 1) = "" Then
Sheets("Auditliste").Cells(4, 3) = ""
Else
Sheets("Auditliste").Cells(4, 3) = "=FFZ!D12"
End If
If Sheets("FFZ").Cells(13, 1) = "" Then
Sheets("Auditliste").Cells(5, 3) = ""
Else
Sheets("Auditliste").Cells(5, 3) = "=FFZ!D13"
End If
If Sheets("FFZ").Cells(14, 1) = "" Then
Sheets("Auditliste").Cells(6, 3) = ""
Else
Sheets("Auditliste").Cells(6, 3) = "=FFZ!D14"
End If
If Sheets("FFZ").Cells(15, 1) = "" Then
Sheets("Auditliste").Cells(7, 3) = ""
Else
Sheets("Auditliste").Cells(7, 3) = "=FFZ!D15"
End If
If Sheets("FFZ").Cells(16, 1) = "" Then
Sheets("Auditliste").Cells(8, 3) = ""
Else
Sheets("Auditliste").Cells(8, 3) = "=FFZ!D16"
End If
If Sheets("FFZ").Cells(17, 1) = "" Then
Sheets("Auditliste").Cells(9, 3) = ""
Else
Sheets("Auditliste").Cells(9, 3) = "=FFZ!D17"
End If
If Sheets("FFZ").Cells(18, 1) = "" Then
Sheets("Auditliste").Cells(10, 3) = ""
Else
Sheets("Auditliste").Cells(10, 3) = "=FFZ!D18"
End If
If Sheets("FFZ").Cells(19, 1) = "" Then
Sheets("Auditliste").Cells(11, 3) = ""
Else
Sheets("Auditliste").Cells(11, 3) = "=FFZ!D19"
End If
If Sheets("FFZ").Cells(20, 1) = "" Then
Sheets("Auditliste").Cells(12, 3) = ""
Else
Sheets("Auditliste").Cells(12, 3) = "=FFZ!D20"
End If
If Sheets("FFZ").Cells(21, 1) = "" Then
Sheets("Auditliste").Cells(13, 3) = ""
Else
Sheets("Auditliste").Cells(13, 3) = "=FFZ!D21"
End If
If Sheets("FFZ").Cells(22, 1) = "" Then
Sheets("Auditliste").Cells(14, 3) = ""
Else
Sheets("Auditliste").Cells(14, 3) = "=FFZ!D22"
End If
If Sheets("FFZ").Cells(23, 1) = "" Then
Sheets("Auditliste").Cells(15, 3) = ""
Else
Sheets("Auditliste").Cells(15, 3) = "=FFZ!D23"
End If
If Sheets("FFZ").Cells(24, 1) = "" Then
Sheets("Auditliste").Cells(16, 3) = ""
Else
Sheets("Auditliste").Cells(16, 3) = "=FFZ!D24"
End If
If Sheets("FFZ").Cells(25, 1) = "" Then
Sheets("Auditliste").Cells(17, 3) = ""
Else
Sheets("Auditliste").Cells(17, 3) = "=FFZ!D25"
End If
If Sheets("FFZ").Cells(26, 1) = "" Then
Sheets("Auditliste").Cells(18, 3) = ""
Else
Sheets("Auditliste").Cells(18, 3) = "=FFZ!D26"
End If
If Sheets("FFZ").Cells(27, 1) = "" Then
Sheets("Auditliste").Cells(19, 3) = ""
Else
Sheets("Auditliste").Cells(19, 3) = "=FFZ!D27"
End If
If Sheets("FFZ").Cells(28, 1) = "" Then
Sheets("Auditliste").Cells(20, 3) = ""
Else
Sheets("Auditliste").Cells(20, 3) = "=FFZ!D28"
End If
If Sheets("FFZ").Cells(29, 1) = "" Then
Sheets("Auditliste").Cells(21, 3) = ""
Else
Sheets("Auditliste").Cells(21, 3) = "=FFZ!D29"
End If
If Sheets("FFZ").Cells(30, 1) = "" Then
Sheets("Auditliste").Cells(22, 3) = ""
Else
Sheets("Auditliste").Cells(22, 3) = "=FFZ!D30"
End If
End Sub
'Auditliste aktuell halten - Spalte E - UVV
Private Sub SpalteE()
If Sheets("FFZ").Cells(10, 5) = "" Then
Sheets("Auditliste").Cells(2, 4) = ""
Else
Sheets("Auditliste").Cells(2, 4) = "=FFZ!E10"
End If
If Sheets("FFZ").Cells(11, 5) = "" Then
Sheets("Auditliste").Cells(3, 4) = ""
Else
Sheets("Auditliste").Cells(3, 4) = "=FFZ!E11"
End If
If Sheets("FFZ").Cells(12, 5) = "" Then
Sheets("Auditliste").Cells(4, 4) = ""
Else
Sheets("Auditliste").Cells(4, 4) = "=FFZ!E12"
End If
If Sheets("FFZ").Cells(13, 5) = "" Then
Sheets("Auditliste").Cells(5, 4) = ""
Else
Sheets("Auditliste").Cells(5, 4) = "=FFZ!E13"
End If
If Sheets("FFZ").Cells(14, 5) = "" Then
Sheets("Auditliste").Cells(6, 4) = ""
Else
Sheets("Auditliste").Cells(6, 4) = "=FFZ!E14"
End If
If Sheets("FFZ").Cells(15, 5) = "" Then
Sheets("Auditliste").Cells(7, 4) = ""
Else
Sheets("Auditliste").Cells(7, 4) = "=FFZ!E15"
End If
If Sheets("FFZ").Cells(16, 5) = "" Then
Sheets("Auditliste").Cells(8, 4) = ""
Else
Sheets("Auditliste").Cells(8, 4) = "=FFZ!E16"
End If
If Sheets("FFZ").Cells(17, 5) = "" Then
Sheets("Auditliste").Cells(9, 4) = ""
Else
Sheets("Auditliste").Cells(9, 4) = "=FFZ!E17"
End If
If Sheets("FFZ").Cells(18, 5) = "" Then
Sheets("Auditliste").Cells(10, 4) = ""
Else
Sheets("Auditliste").Cells(10, 4) = "=FFZ!E18"
End If
If Sheets("FFZ").Cells(19, 5) = "" Then
Sheets("Auditliste").Cells(11, 4) = ""
Else
Sheets("Auditliste").Cells(11, 4) = "=FFZ!E19"
End If
If Sheets("FFZ").Cells(20, 5) = "" Then
Sheets("Auditliste").Cells(12, 4) = ""
Else
Sheets("Auditliste").Cells(12, 4) = "=FFZ!E20"
End If
If Sheets("FFZ").Cells(21, 5) = "" Then
Sheets("Auditliste").Cells(13, 4) = ""
Else
Sheets("Auditliste").Cells(13, 4) = "=FFZ!E21"
End If
If Sheets("FFZ").Cells(22, 5) = "" Then
Sheets("Auditliste").Cells(14, 4) = ""
Else
Sheets("Auditliste").Cells(14, 4) = "=FFZ!E22"
End If
If Sheets("FFZ").Cells(23, 5) = "" Then
Sheets("Auditliste").Cells(15, 4) = ""
Else
Sheets("Auditliste").Cells(15, 4) = "=FFZ!E23"
End If
If Sheets("FFZ").Cells(24, 5) = "" Then
Sheets("Auditliste").Cells(16, 4) = ""
Else
Sheets("Auditliste").Cells(16, 4) = "=FFZ!E24"
End If
If Sheets("FFZ").Cells(25, 5) = "" Then
Sheets("Auditliste").Cells(17, 4) = ""
Else
Sheets("Auditliste").Cells(17, 4) = "=FFZ!E25"
End If
If Sheets("FFZ").Cells(26, 5) = "" Then
Sheets("Auditliste").Cells(18, 4) = ""
Else
Sheets("Auditliste").Cells(18, 4) = "=FFZ!E26"
End If
If Sheets("FFZ").Cells(27, 5) = "" Then
Sheets("Auditliste").Cells(19, 4) = ""
Else
Sheets("Auditliste").Cells(19, 4) = "=FFZ!E27"
End If
If Sheets("FFZ").Cells(28, 5) = "" Then
Sheets("Auditliste").Cells(20, 4) = ""
Else
Sheets("Auditliste").Cells(20, 4) = "=FFZ!E28"
End If
If Sheets("FFZ").Cells(29, 5) = "" Then
Sheets("Auditliste").Cells(21, 4) = ""
Else
Sheets("Auditliste").Cells(21, 4) = "=FFZ!E29"
End If
If Sheets("FFZ").Cells(30, 5) = "" Then
Sheets("Auditliste").Cells(22, 4) = ""
Else
Sheets("Auditliste").Cells(22, 4) = "=FFZ!E30"
End If
End Sub