Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Label
BildScreenshot zu Label Label-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema MultiPage
BildScreenshot zu MultiPage MultiPage-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Listbox filtern

Betrifft: Listbox filtern von: Linus
Geschrieben am: 28.08.2014 10:24:57

Hallo alle zusammen,

ich habe folgendes Problem.

Ich habe eine Userform erstellt zur Verwaltung von Mitarbeitern. Soweit funktioniert auch alles.

Nun würde ich gerne die Listbox, welche mit die Mitarbeiter anzeigt, aufgrund der Eingabe in eine Textbox filtern.

Sprich, wenn ich folgende Mitarbeiter habe

Müller Thomas
Meyer Frank
Mustermann Max
Test Torsten

und in die TextBox "M" eingebe, sollten nur noch die Mitarbeiter mit M beginnend in der Listbox angezeigt werden. Schreibe ich "Me" nur noch Meyer Frank, usw.

Hat da jemand eine Lösung für mich?

Vielen Dank bereits im Voraus.

  

Betrifft: AW: Listbox filtern von: yummi
Geschrieben am: 28.08.2014 11:08:19

Hallo Linus,

wenn deine Listbox eine Kombobox sein darf, kannst Du unter MatchEntry auf fmMatchFirstLetter einstellen, das ist die Funktionalität die du suchst.

Gruß
yummi


  

Betrifft: AW: Listbox filtern von: Linus
Geschrieben am: 29.08.2014 09:13:38

Hallo yummi,

erstmals danke für deine Antwort. Leider bin ich eigentlich ein absoluter Noob in der Programmierung und somit kann ich damit nicht wirklich was anfangen.

Ich habe eher gehofft es kommt jetzt ein Code zum Beispiel :-).

Ich kann auch gern meinen Code hier veröffentlichen wenn das bei der Beantwortung hilft.

Gruß Linus


  

Betrifft: AW: Listbox filtern von: yummi
Geschrieben am: 29.08.2014 10:09:01

Hallo Linux,

ja mach mal dann guck ich mal
Gruß
yummi


  

Betrifft: AW: Listbox filtern von: Linus
Geschrieben am: 01.09.2014 07:46:58

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) < Date - 60 And (ListBox1.List( _
ListBox1.ListIndex, 8) = "Verschrottet" Or ListBox1.List(ListBox1.ListIndex, 8) = "Vergeben") Then
                    
                    CommandButton6.Visible = True
                    Label15.Visible = False
                    
                    
                    Else
                    Label15.Visible = False
                    CommandButton6.Visible = False
                    
                    If ListBox1.List(ListBox1.ListIndex, 8) = "Verschrottet" Or ListBox1.List( _
ListBox1.ListIndex, 8) = "Vergeben" Then
                    
                    Label15.Visible = True
                    Label15.Caption = "Noch " & 60 - (CDbl(Date) - CDbl(ListBox1.List(ListBox1. _
ListIndex, 9))) & " Tage bis der Löschvorgang möglich ist!"
                   
                                    
                                                           
                    Else
                    Label15.Visible = False
                    CommandButton6.Visible = False
                    End If
                    End If
                    
                    
                    'UVV Überprüfung
                    
                    If ListBox1.List(ListBox1.ListIndex, 4) = "" Or ListBox1.List(ListBox1. _
ListIndex, 4) < Date Then
                    
                    If ListBox1.List(ListBox1.ListIndex, 4) = "" Then
                    Label19.Visible = True
                    Label19.ForeColor = vbRed
                    Label19.Caption = "ACHTUNG! Bei diesem Fahrzeug fehlen die UVV angaben!!"
                    Else
                    
                    If ListBox1.List(ListBox1.ListIndex, 4) < Date Then
                    Label19.Visible = True
                    Label19.ForeColor = vbRed
                    Label19.Caption = "ACHTUNG! Bei diesem Fahrzeug ist die UVV abgelaufen!!"
                    End If
                    End If
                    Else
                    Label19.Visible = False
                    End If
                    
                    'Einstellung der UVV Warunung
                    
                    If CommandButton6.Visible = True Or Label15.Visible = True Then
                    Label19.Top = 326
                    Else
                    Label19.Top = 264
                    End If
                    
                    
                    
                    End Sub
                    
                    
'Fahrzeug löschen

                    Private Sub CommandButton6_Click()
                                        
                    If MsgBox("Möchten Sie das Fahrzeug endgültig LÖSCHEN?" & Chr(13) & Chr(13)  _
& ListBox1.List(ListBox1.ListIndex, 1), vbYesNo, "Fahrzeug - LÖSCHEN") = vbYes Then
                                        
                    Dim intRow As Integer
                    intRow = ListBox1.ListIndex + 10
                    Sheets("FFZ").Rows(intRow).Delete Shift:=xlUp
                    FFZ.Repaint
                    Else
                    End If
                    
                    SpalteA
                    SpalteC
                    SpalteD
                    SpalteE
                
                
                    End Sub

'Abbruch

                    Private Sub CommandButton5_Click()
                    
                    
                    UserForm_Initialize
                    
                    'MultiPage1.Pages(0).Enabled = 1
                    'MultiPage1.Pages(1).Enabled = 0
                    'FFZ.MultiPage1.Value = 0
                    
                    'Größe_Anfang
                    
                    End Sub

' Größen einstellen
                    
                    Private Sub Größe_Anfang()
                    
                    Height = 582
                    Width = 991
                    
                    ListBox1.Height = 441
                    ListBox1.Width = 740
                    
                    MultiPage1.Height = 480
                    MultiPage1.Width = 768
                    
                    Label14.Left = 828
                    Label82.Left = 534
                    
                    TextBox4.Left = 619
      
                    
                    
                    End Sub
                    
                    Private Sub Größe_Ende()
                    
                    Height = 460
                    Width = 575
                                       
                    MultiPage1.Height = 366
                    MultiPage1.Width = 366
                    
                    Label14.Left = 415
                    
                    
                    End Sub
     
'Speichern & Schließen
     
                         Private Sub CommandButton7_Click()
                        Unload Me
                        ThisWorkbook.Save
                        Application.Quit
                    End Sub

'Auditliste drucken

                    Private Sub CommandButton8_click()
                    
                    On Error GoTo FehlerDrucken:
                    
                    SpalteA
                    SpalteC
                    SpalteD
                    SpalteE
                    
                    If MsgBox("Möchten Sie eine Auditliste drucken?", vbYesNo, "Drucken") =  _
vbYes Then
                    Sheets("Auditliste").PrintOut Copies:=1, Collate:=True, _
                    IgnorePrintAreas:=False
                    Else
                    End If
                    
                    Exit Sub
                    
FehlerDrucken:                     MsgBox "Bitte überprüfen Sie die Standardeinstellungen des  _
Druckers!"
                    
                    End Sub
                    
'Auditliste PDF erstellen


                    Private Sub CommandButton9_Click()
                    
                    On Error GoTo FehlerPDF:
                    
                    SpalteA
                    SpalteC
                    SpalteD
                    SpalteE
                    
                    Sheets("Auditliste").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    "Z:\P\PZ\PZL\PZL_Leitung\05_Meistereien\PZL 53\01_Meisterordner\Datenbank\ _
Auditliste-PZL 53.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
                    
                    
                    Dim Datei As String
                    Datei = "Z:\P\PZ\PZL\PZL_Leitung\05_Meistereien\PZL 53\01_Meisterordner\ _
Datenbank\Auditliste-PZL 53.pdf"
                    ActiveWorkbook.FollowHyperlink Datei
                    
                    Exit Sub
                   
FehlerPDF:                    MsgBox "Bitte überprüfen Sie die Verlinkungen!"
                    
                    End Sub
                    
'Suchfeld... zur Suche von Fahrzeugen

                    Private Sub TextBox4_Change()
                    
                    TextBox4.Text = Left(TextBox4.Text, 3)
                    
                      Dim i As Integer, ii As Integer
                      Dim vntList, strTxt As String, arrSelected()
                      strTxt = LCase(TextBox4)
                      vntList = ListBox1.List
                      ReDim arrSelected(ListBox1.ListCount - 1)
                      For i = 0 To ListBox1.ListCount - 1
                        For ii = 0 To ListBox1.ColumnCount - 1
                          arrSelected(i) = InStr(LCase(vntList(i, ii)), strTxt) > 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




 

Beiträge aus den Excel-Beispielen zum Thema "Listbox filtern"