Auf Auswahllisten beziehen?!

Dieses Thema Auf Auswahllisten beziehen?! im Forum "Microsoft Office Suite" wurde erstellt von Mischi89, 18. Mai 2006.

Thema: Auf Auswahllisten beziehen?! Hi, ich habe ein Tabellenblatt und von B4-I6 Auswahllisten (auch von B8-I10 ; B12-I14 ; B16 - I18 ; B20 - I22 ; B24...

  1. Hi, ich habe ein Tabellenblatt und von B4-I6 Auswahllisten (auch von B8-I10 ; B12-I14 ; B16 - I18 ; B20 - I22 ; B24 - I26 ; B28 - I30) eingefügt. alle Listen mit den selben Inhalten (ca 20 Namen). Spalte B und C hab ich in Zeile 1 zusammengeführt um den Tabellenkopf in B2 und C2 in Frühschicht und Spätschicht zu unterteilen (dasselbe auch mit D und E).

    also es sieht ca so aus:

    A B C D E F G H I
    1 Woche bla bla2 bla3 bla4 bla5 bla6
    2 früh spät früh spät
    3
    4Montag
    5
    6
    7
    8Dienstag
    9
    10
    11
    12Mittwoch
    13
    14
    15
    16Donnerstag
    17
    18
    19Freitag
    20
    21
    22
    23
    24Samstag

    Nun möchte ich über die Listen die Namen eintragen. kann man das irgendwie so machen, dass wenn ich Montag bei bla zu frühschicht jemand drin stehen habe, dass er dann bei bla 2 und bei bla dann auch nich mehr in der liste zur frühschicht anwählbar ist? genauso mit bla3, bla4, bla5 und bla6! wenn ich bei bla3 2 mann reinsetze, sollen die bei bla, bla2, bla4, bla5 und bla6 auch net mehr anwählbar sein in der Liste. und das halt mit allen tagen xD

    ich hoffe ihr könnt mir helfn!!!!!!!!

    eigentlich klingts schon nach wenn-dann-funktionen: wenn in B4 Frau Mücke, dann in B5,B6,D5 und D6 keine Frau Mücke in der Liste anwählbar xD
     
  2. Hallo Mischi89,

    ich hab dir mal ein Beispiel zusammengestellt, wie man das per Makro lösen kann.

    Lege einfach eine neue Mappe an und verfahre wie in den beiden Makro-Teilen beschrieben.

    Prinzip:
    a) es wird ein Defaulwert für die Zellen festgelegt
    b) es wird eine Namensliste definiert
    (alle anderen Namen werden wieder gelöscht)
    c) der Bereich mit den DropDownfeldern wird auf Änderung überwacht.

    bei Änderung:
    1) wird geschaut, welche von den gültigen Namen bereits benutzt werden. Der übrige Rest der gültigen Namen ist die anzuzeigende Liste.
    2) steht in einer Zelle ein gültiger Name, wird der anzuzeigende Liste dieser Zelle dieser Name hinzugefügt.

    Mit Formeln ist das auch machbar, nur bräuchte das etwas mehr Aufwand.

    Gruß Matjes :)

    Beispiel für einen Bereich B5:C14 auf Blatt->Tabelle1'.

    Nach dem Einbau der makros bitte in B5 irgendetwas eingeben und Return betätigen. Dann wird der makro aktiv ;)

    in ein Modul packen:
    Code:
    Option Explicit
     ->***
     ->*** Im Bereich ZuUeberwachenderBereich werden die Zellen
     ->*** mit einer Gültigkeits-Liste versehen
     ->***
     ->*** Die Gültigkeitsliste enthält die Namen, die im Bereich
     ->*** ZuUeberwachenderBereich noch nicht verwendet werden.
     ->*** Enthält eine Zelle bereits einen gültigen Namen,
     ->*** wird der Gültigkeitsliste dieser zelle dieser Name noch
     ->*** hinzugefügt.
     ->***
     ->*** DIESES MAKRO muß in ein Modul der Arbeitsmappe gelegt werden.
     ->***
     ->Anleitung:
     ->
     ->a) betreffende Excel-Datei öffnen
     ->b) mit Alt+F11 VB-Editor öffnen
     ->c) im Project-Fenster VBAProject(betreffende Excel-Datei) selektieren
     ->d) rechte Maustaste-> einfügen Modul
     ->e) per Copy and Paste diesen Code einfügen
     ->f) Speichern (mit Strg+S)
     ->g) VB-Editor schliessen (mit Alt+Q)
    
      
     ->Name und Bereich des zu überwachenden Blattes
      Const c_BlattName = Tabelle1
      Public Const ZuUeberwachenderBereich = B5:C14
      
     ->Globale Variable
      Dim vAlleNamen As Variant
      Dim fAusgewaehlteNamen() As String, fAusgewaehlteNamen_cnt As Long
      Dim fAnzeigeListe() As String, fAnzeigeListe_cnt As Long
      
     ->Default-Wert für Gültigkeits-Liste
      Const c_keinNameAusgewaehlt = noch auszuwählen
    
    Sub DropDownListenNamenSetzen()
    
      Dim ws As Worksheet
    
     ->*** Feld, das alle Namen aufnimmt
      vAlleNamen = Array(Name1, Name2, Name3, Name4, Name5, _
                         Name6, Name7, Name8, Name9, Name10)
    
      On Error Resume Next
      Set ws = ActiveWorkbook.Worksheets(c_BlattName)
      If Err.Number <> 0 Then
        Err.Clear: On Error GoTo 0
        MsgBox Blatt  & c_BlattName &  nicht vorhanden., vbExclamation
        GoTo AUFRAEUMEN
      End If
      On Error GoTo 0
      
     ->*** Bereich auf ausgewählte Namen prüfen
      Call AusgewaehlteNamenZusammenstellen(c_keinNameAusgewaehlt, _
                                             fAusgewaehlteNamen(), _
                                             fAusgewaehlteNamen_cnt)
      
     ->*** Anzeige Liste Zusammenstellen
      Call AnzeigeListeZusammenstellen( _
                    vAlleNamen, _
                    fAusgewaehlteNamen(), fAusgewaehlteNamen_cnt, _
                    c_keinNameAusgewaehlt, _
                    fAnzeigeListe(), fAnzeigeListe_cnt)
      
      Call GueltigkeitslisteImBereichFuerJedeZelleNeuSetzen( _
                                ws, ZuUeberwachenderBereich, _
                                vAlleNamen, _
                                c_keinNameAusgewaehlt, _
                                fAnzeigeListe(), fAnzeigeListe_cnt)
      
    AUFRAEUMEN:
      Set ws = Nothing
    End Sub
    Private Function GueltigkeitslisteImBereichFuerJedeZelleNeuSetzen( _
                ws As Worksheet, ZuUeberwachenderBereich As String, _
                vAlleNamen As Variant, _
                c_keinNameAusgewaehlt As String, _
                fAnzeigeListe() As String, fAnzeigeListe_cnt As Long)
    
      Dim Zelle As Range, sName As String, sNamensliste As String
      Dim x As Long, b_gefunden As Boolean, sEintragListe As String
      
     ->Events abschalten
      Application.EnableEvents = False
      
      sNamensliste = 
      For x = 1 To fAnzeigeListe_cnt
        If x <> fAnzeigeListe_cnt Then
          sNamensliste = sNamensliste & fAnzeigeListe(x) & ,
        Else
          sNamensliste = sNamensliste & fAnzeigeListe(x)
        End If
      Next
      For Each Zelle In ws.Range(ZuUeberwachenderBereich)
       ->Namen merken
        sName = Zelle.Value
        If sName =  Or sName = c_keinNameAusgewaehlt Then
          Zelle.Value = c_keinNameAusgewaehlt
          sEintragListe = sNamensliste
        Else
          b_gefunden = False
          For x = LBound(vAlleNamen) To UBound(vAlleNamen)
            If sName = vAlleNamen(x) Then
              b_gefunden = True
              Exit For
            End If
          Next
         ->wenn bereits ein gültiger Name ausgewählt ist,
         ->dann der Liste hinzufügen
          If b_gefunden Then
            sEintragListe = sName & , & sNamensliste
          Else
            sEintragListe = sNamensliste
            Zelle.Value = c_keinNameAusgewaehlt
          End If
        End If
       ->Fehler abfangen, falls noch keine Validierung vorhanden ist
        On Error Resume Next
       ->Validierung löschen
        Zelle.Validation.Delete
        On Error GoTo 0
       ->Validierung neu eintragen
        Zelle.Validation.Add Type:=xlValidateList, Formula1:=sEintragListe
      Next
    
    AUFRAEUMEN:
     ->Events anschalten
      Application.EnableEvents = True
      Set Zelle = Nothing
    End Function
    
    Private Function AnzeigeListeZusammenstellen( _
                    vAlleNamen As Variant, _
                    fAusgewaehlteNamen() As String, fAusgewaehlteNamen_cnt As Long, _
                    c_keinNameAusgewählt As String, _
                    fAnzeigeListe() As String, fAnzeigeListe_cnt As Long)
    
      
      Dim x As Long, y As Long, b_gefunden As Boolean
      
     ->Liste initialisieren
      ReDim fAnzeigeListe(1 To 1): fAnzeigeListe_cnt = 0
      
     ->nicht ausgewählt an 1. Stelle
      fAnzeigeListe_cnt = fAnzeigeListe_cnt + 1
      ReDim Preserve fAnzeigeListe(1 To fAnzeigeListe_cnt)
      fAnzeigeListe(fAnzeigeListe_cnt) = c_keinNameAusgewählt
      
     ->Namen aus AlleNamen in fAnzeigeListe(), wenn nicht bereits ausgewählt
      For x = LBound(vAlleNamen) To UBound(vAlleNamen)
        b_gefunden = False
        For y = 1 To fAusgewaehlteNamen_cnt
          If vAlleNamen(x) = fAusgewaehlteNamen(y) Then
            b_gefunden = True
            Exit For
          End If
        Next
        If Not b_gefunden Then
          fAnzeigeListe_cnt = fAnzeigeListe_cnt + 1
          ReDim Preserve fAnzeigeListe(1 To fAnzeigeListe_cnt)
          fAnzeigeListe(fAnzeigeListe_cnt) = vAlleNamen(x)
        End If
      Next
      
    End Function
    Private Function AusgewaehlteNamenZusammenstellen(c_keinNameAusgewaehlt As String, _
                    fAusgewaehlteNamen() As String, fAusgewaehlteNamen_cnt As Long)
    
      Dim Zelle As Range
      Dim sName As String
    
     ->*** füllt die globale Liste AusgewaehlteNamen
     ->Feld AusgewaehlteNamen initialisieren
      fAusgewaehlteNamen_cnt = 0
      ReDim fAusgewaehlteNamen(1 To 1)
     ->zu überwachenden Bereich durchlaufen
      For Each Zelle In ActiveSheet.Range(ZuUeberwachenderBereich)
        sName = Zelle.Value
        If sName <>  And sName <> c_keinNameAusgewaehlt Then
         ->Namen merken
          fAusgewaehlteNamen_cnt = fAusgewaehlteNamen_cnt + 1
          ReDim Preserve fAusgewaehlteNamen(1 To fAusgewaehlteNamen_cnt)
          fAusgewaehlteNamen(fAusgewaehlteNamen_cnt) = sName
        End If
      Next
    AUFRAEUMEN:
      Set Zelle = Nothing
    End Function
    in die Code-Seite von->Tabelle1':
    Code:
    Option Explicit
     ->*** DIESES MAKRO muß in der Code-Seite des betreffenden Arbeitsblattes liegen.
     ->***
     ->Anleitung:
     ->
     ->a) betreffende Excel-Datei öffnen
     ->b) betreffendes Blatt aktivieren
     ->c) Code-Seite des Tabellenblattes im VB-Editor öffnen
     ->Dazu mit rechter Maustaste auf die Blattlasche klicken
     ->und->Code anzeigen' wählen
     ->d) per Copy and Paste diesen Code einfügen
     ->e) Speichern (mit Strg+S)
     ->f) VB-Editor schliessen (mit Alt+Q)
      
      
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Dim r As Range
     ->Schnittmenge von Target und ZuUeberwachenderBereich
      Set r = Application.Intersect(ActiveSheet.Range(ZuUeberwachenderBereich), Target)
     ->Liegt Target im überwachten Bereich ?
     ->ja-> dann
      If Not r Is Nothing Then
       ->ja-> dann Gültigkeitslisten neu setzen
        Call DropDownListenNamenSetzen
      End If
      Set r = Nothing
    End Sub
     
  3. Hi, danke erstmal für deine Hilfe!! ich habe aber noch einige Fragen bezüglich des Moduls, weil ich von sowas wirklich 0 plan hab :-\

    1. wie änder ich den Bereich? ich habe es so probiert:

    Code:
    Public Const ZuUeberwachenderBereich = (B4:I6B8:I10B12:I14B16:I18B20:I22B24:I26B28:I30)
    hatte es erst mit , aba da kam ne fehlermeldung, genau so wie bei dem jetzigen beispiel...
    was mach ich falsch?

    die Namen hab ich so geändert:
    Code:
    vAlleNamen = Array(Angela, Katharina, Katrin, Tanja, Petra, Fr. Sternberg, Fr. Völz, Ina, Susan, Fr. China, Fr. Ruppert, Mandy, Fr. Kaniuth, Doreen, Kathleen, Marion, Madeleine)
    bei

    '
    Code:
    Default-Wert für Gültigkeits-Liste
    Const c_keinNameAusgewaehlt = noch auszuwählen
    weiss ich gar net was ich reinschreiben soll bzw was das bezweckt... ich hab einfach mal Hans reingeschrieben!
    wenn ich jetz bei B4 was reinschreibe kommt Laufzeitfehler 1004: anwendungs oder objektdefinierter Fehler

    und es verweist auf die Code-Seite von Tabelle1 auf diese zeile:
    Set r = Application.Intersect(ActiveSheet.Range(ZuUeberwachenderBereich), Target)

    für mich als noob klingt das nach den falsch eingetragenen bereichen irgendwie... naja ich hoff ihr könnt mir helfn!!! :| :| :| :| :| :| :|


    edit: ich muss bis heute 16uhr fertig damit sein! schnelle Hilfe wär am besten!!
     
  4. Hallo Michi,

    schick mir mal die Tabelle an mein mail-addy, dann schau ich heute Mittag, was da falsch läuft.

    Gruß Matjes :)
     
  5. hab ich übrigens.... (vor ca. 2 stunden)
     
  6. ist auch gerade ausgepackt worden. ;D

    Antwort demnächst

    gruß matjes :)
     
  7. jo ich hab mehrere Beispiele wie ich die pläne machen könnte, kann die ja alle mal schicken ^^
     
Die Seite wird geladen...

Auf Auswahllisten beziehen?! - Ähnliche Themen

Forum Datum
Excel 2002 -> erstellen von Auswahllisten, die weitere Daten ziehen Windows XP Forum 24. Mai 2007
DHCP-Problem / Adresse beziehen Windows XP Forum 4. Feb. 2013
(Wie) kann eine Excel-Formel die eigene Zeile in die Berechnung einbeziehen? Windows XP Forum 6. Nov. 2010
Netzwerkadresse beziehen?eingeschränkte oder keine Konnektivität? Windows XP Forum 18. Jan. 2009
Netzwerkaddresse beziehen Windows XP Forum 24. Sep. 2008