Microsoft Excel

Herbers Excel/VBA-Archiv

dynamisches Dropdown


Betrifft: dynamisches Dropdown von: Steve
Geschrieben am: 10.09.2019 15:46:31


Moin Leute,

ich habe mir da so eine Idee in den Kopf gesetzt, weiss aber nicht ob das grundsätzlich funktioniert. (Deshalb gibt es auch keine Beispieldatei)

Folgendes stelle ich mir vor.

Ich erstelle per Knopfdruck ein neues Sheet. (Das kann ich schon)

In dem neuen Sheet befindet sich eine Zelle mit Dropdown.
Sagen wir mal ich kann dort Geräte aussuchen.

Nun wird das nächste Sheet per Knopfdruck generiert.
Die Liste der Dropdowns soll nun um das eine ausgewählte reduziert sein.

Wenn ich dann per Knopfdruck irgendein Sheet archiviere, soll das hinterlegte Gerät wieder freigegeben werden.

Ist so etwas möglich? Hat da jemand schonmal sowas umgesetzt?
Hat jemand vielleicht irgendwo eine Beispieldatei?

Wie gesagt, ist nur eine fixe Idee.

(Sagen wir mal die Quelle des Dropdowns befindet sich in einer dynamischen Liste auf einem versteckten Sheet das ich wie immer "DATEN" nenne.)

Liebe Grüße

Steve

  

Betrifft: AW: dynamisches Dropdown von: 1712294.html
Geschrieben am: 10.09.2019 16:11:38

Hi Steve,

wenn ich dich richtig verstanden habe, dann...ja, das geht!

Dein verstecktes Blatt hat die Daten für Dropdowns, z Bsp

Bohrer
5mm
6mm
7mm
usw

Hammer
Schlaghammer
Dachdeckerhammer
usw

Dein 1. Knopfdruck = 1. neues Sheet hat zur Folge, dass im Dropdown von Sheet1 nur die Kategorien gelistet sind, wie hier = Bohrer,Hammer
Hier wählst du "Bohrer" aus, z Bsp.

Nächster Klick auf den Knopf erzeugt Sheet2.
In Sheet2 werden nun nur die Einträge angezeigt, die mit Dropdownwahl (Bohrer) in Sheet1 in Verbindung stehen...also...5mm,6mm,7mm

Wenn ich das richtig verstanden habe, und dir weiterhelfen soll, benötige ich aber eine Bsp-Datei mit allen Bsp-Daten und eben deinen Layouts der Tabellen.

Hilfts?

Ciao
Thorsten

  

Betrifft: AW: dynamisches Dropdown von: 1712311.html
Geschrieben am: 10.09.2019 17:06:06

Moin!
Mal eine Frage zum Freigeben. Wenn du ein Sheet archivierst, soll der freigegebene Wert dann wieder in allen andern Blättern erscheinen oder nur in neu angelegten?
Was soll passieren, wenn in den Blättern nix ausgewählt wurde oder die Auswahl geändert wurde?
Wäre es ggf. nicht besser, du wählst beim Erstellen schon den Wert aus, so das er fest auf dem Blatt steht und dort nicht mehr geändert werden kann!?
Grundsätzlich sollten deine Vorstellungen gehen. Fraglich sind halt nur die Abläufe.
VG

  

Betrifft: AW: dynamisches Dropdown von: 1712470.html
Geschrieben am: 11.09.2019 14:39:51

Moin Oberschlumpf und Moin Matthias,

Ich merke schon, ich muss noch präziser Fragen und Erklären. Das möchte ich mal versuchen.

Wenn ich Oberschlumpf richtig verstanden habe, dann meint er ein Dropdown das in Bezug zum zweiten steht.
Das kenne ich unter INDIREKT() in der Datenüberprüfung.
Das ist von mir nicht gemeint.

Ich versuche es nochmal, aber hoffentlich präziser und hänge eine Datei zur Veranschaulichung an. Allerdings habe ich zunächst mal auf das Makro für die neuen Arbeitsblätter verzichtet.

https://www.herber.de/bbs/user/131976.xlsx

Eigentlich möchte ich sicherstellen, das ich jeden Dropdowneintrag wirklich nur ein einziges Mal verwenden kann.

Ich erstelle also ein neues Arbeitsblatt und habe, weil es das erste ist, sagen wir mal zur Auswahl die Zahlen von 1-10. Ich wähle die 1 aus.

Nun erstelle ich ein zweites Arbeitsblatt und habe in dem Dropdownfeld nur noch die Auswahl zwischen 2-10. Ich wähle die 5

USW. USW

Nun zu deinen Fragen Matthias.

Sobald in irgendeinem Blatt die Auswahl geändert wurde, oder das Blatt archiviert wurde, soll es möglich sein, sowohl in den bereits existierenden, wie auch in den neuen Blättern diesen Wert wieder zu verwenden.

Beispiel:
Sagen wir mal ich archiviere das erste Blatt. Dieses hatte ja als Dropdown die 1 ausgewählt.
Dann soll es in jede neuen Blatt, aber auch in dem schon existierenden zweiten Blatt möglich sein die 1 Auszuwählen.

Zu deiner Frage mit dem festen Vergeben des Wertes. Das hatte ich schon verworfen, da die Werte sich zu oft ändern. Mein Ziel ist es eine KFZ Kartei (Pro Blatt ein KFZ) zu entwerfen.
Innerhalb jedes Blattes möchte ich jedem KFZ Geräte (anhand Seriennummer bzw Zuordnungsnummer) zuordnen. Diese müssen aber veränderbar bleiben, da manche Geräte auch mal ausgetauscht (wegen Reparatur usw.) müssen.

Okay, ich hoffe ich habe nichts vergessen.



Liebe Grüße

Steve

  

Betrifft: AW: dynamisches Dropdown von: 1712509.html
Geschrieben am: 11.09.2019 16:55:35

Moin!
Hier mal deine Datei zurück. Habe unter DieseArbeitsmappe einen Code eingefügt. Der sollte deinen Wunsch bewerkstelligen. Ich habe als Bezugszelle jeder Seite mal die Zelle A3 genommen. Das kannst du in dem Makro ganz oben bei der Konstanten einfach ändern. Der Code gilt für neue Blätter als auch für angelegte. Dein Archivieren habe ich mal als entfernen des Blattes gedeutet. Da mein Ex noch kein SheetBeforeDelete Event kennt, müsstest du in deinem Code oder per SChaltfläche beim Löschen den gleichen Code wie im Workbook_SheetChange ausführen. Einfach mal probieren.
https://www.herber.de/bbs/user/131992.xlsm
VG

  

Betrifft: AW: dynamisches Dropdown von: 1712511.html
Geschrieben am: 11.09.2019 17:00:34

Moin!
Ich nochmal. Bzgl. des Blattlöschens, kannst du den Code auch in SheetActivate reinkopieren. Dann sollte er auch den Löschen abfangen.
VG

  

Betrifft: AW: dynamisches Dropdown von: 1712872.html
Geschrieben am: 13.09.2019 12:09:51


Moin Matthias,

das ist ja großartig. Genau das habe ich gesucht.

Einige Fragen habe ich aber noch dazu.

1. Wenn ich nun eine Zweite Auswahltabelle habe. Sagen wir die erste ist ein KFZ kennzeichen mit der Konstanten auf A3 (also so wie von dir hergestellt.) und nun eine zweite mit telefonnummern (das Handy auf dem Fahrzeug) mit Konstante auf B3. Sehe ich das richtig, das ich mir dann nur

Function auswahlerstellen anschauen muss?

2. Was müsste ich tun, wenn ich alle Dropdowns auf einem Sheet haben möchte?
Schaue ich mir dann Const Zelle = an? Habe versucht da was herumzuspielen, aber leider hat das nicht geklappt. Habe einfach eine weitere Zelle eingegeben und mit "," getrennt. Leider erfolglos.

3. Neuer Bereich für mich
Makros in Dieses Arbeitsblatt sind für mich was ganz neues. Ich habe versucht wie du gesagt hast Workbook_SheetChange in ein Löschen Code einzufügen aber irgendwie will das nicht.
Kann es sein das man das noch anpassen muss? Mihc verwirrt das "Private " vo dem Sub und die gefüllte () nach dem Sub.

Liebe Grüße

Steve

  

Betrifft: AW: dynamisches Dropdown von: 1712963.html
Geschrieben am: 13.09.2019 16:01:05

Moin!
zu 1.
Nicht ganz. Da du für zwei unterschiedliche Zellen ie Gütligkeit erstellt, müsstest du in jeder Prozedur von DieseArbeitsmappe was ändern.
Zuerst mal eine Konstante B3 am Anfang anlagen. In NewSheet und SheetChange dann den Teil im with kopieren und gleich darunter einfügen. Im Zweiten (eingefügten) dann zelle durch zelle2 ersetzen und am besten auswahlerstllen durch auswahlerstellen2 tauschen.
Dann das auswahlerstellen2 anlegen (selber Code wie die andere) und dort halt den Code auf den neuen Bereich mit den Handys anpassen. Ggf. könnte man das auch zusammen in einer abhandeln. Dann müsste man aber wieder Parameter an die Funktion übergeben.

zu2.
Ist in 1. schon teilweise mit beschrieben. Da unterschiedliche Gültigkeiten musst du auch unterschiedliche Zellen angeben. Sollte die selber Gültigkeit für mehrere Zellen gelten, dann mit Komma trennen. Dann müsstest du aber im Code noch mehr ändern. Die Zeile hier
if target.addree = Zelle then
durch das hier ersetzen
If Not Intersect(Target, Sh.Range(zelle)) Is Nothing Then
Die Frage ist aber wie dann auswahlerstellen funktionieren soll? Alle Bereiche überprüfen oder weiterhin nur A3? Bei der Änderung mit intersect, würde nur A3 ausgewählt aber in alle Zellen die du angegeben hast eingetragen.

zu3.
Die Makros in DieseArbeitsmappe sind genauso Events wie im Blatt. Da kannst du aber u.U. für alle Blätter Events erstellen, die auch auf allen Blättern Änderungen abfangen.
Bzgl. des LÖschens meinte ich. In DieseArbeitsmappe ein Event SheetActivate anlagen und den gleichen Code wie im Change anlegen. Wenn du dann ein Blatt aufmachst werden wieder alle aktualisiert. Falls du beim Archivieren ein Blatt manuell löschst, ggf. den Code noch mit einem Button anstoßen.
Die gefüllten Klammern zeigen nur an, dass dort Parameter übrgeben werden. Mit denen wird dann in der Prozedur / Funktion weitergearbeitet. Das Private sagt nur, dass die Funktion nur in dem Blatt / Bereich im VBE gilt.
VG

  

Betrifft: AW: dynamisches Dropdown von: 1713331.html
Geschrieben am: 16.09.2019 15:25:56

Moin Matthias,

danke dir für die wirklich gute Hilfe zur Selbsthilfe.

Ich habe Frage 1. damit super lösen können und hab nun 2 unterschiedliche Dropdowns auf einem Blatt.

Frage 2. habe ich nach deinen Anweisungen auch umsetzen können und habe deine Fragestellung nun auch begriffen. Ich habe mal A1:A4 mit Dropdowns versehen. Aber nur der Wert aus A1 verschwindet aus der Liste.
Was müsste ich da ändern, damit alle Bereiche überprüft werden?

Eine letzte zusatzfrage habe ich noch:
Es existiert noch ein Modul 1 mit folgendem Code:

Sub Makro1()
   '
   ' Makro1 Makro
   '
   
   '
      Range("A15").Select
       With Selection.Validation
               .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:="=$A$17:$A$24"
           .IgnoreBlank = True
           .InCellDropdown = True
           .InputTitle = ""
           .ErrorTitle = ""
           .InputMessage = ""
           .ErrorMessage = ""
           .ShowInput = True
           .ShowError = True
       End With
   End Sub
Hab das mal auskommentiert. Es funktioniert trotzdem alles. Trugschluss von mir? Oder kann das wech?

Liebe Grüße

Steve
  

Betrifft: AW: dynamisches Dropdown von: 1713333.html
Geschrieben am: 16.09.2019 15:33:34


Ich nochmal

Hab einen Nachbrenner. Das Blatt DATEN hat ja auch die Dropdowns mit drin. Kann ich das Blatt irgendwie ausschließen?

Liebe Grüße

Steve

  

Betrifft: AW: dynamisches Dropdown von: 1713372.html
Geschrieben am: 16.09.2019 20:19:23

Moin!
1. aus Liste verschwinden:
Da müsstest du in der Sub auswahlerstellen noch eine Schleife über alle Zellen in der Range machen. im Code unten mit dabei. Habe den Aus der Alten Datei genommen. Da gibt es aber nur einen Auswahlbereich.

2. Modul 1:
Das kann komplett raus. Da habe ich nur mal schnell die Punkte der Gültigkeit aufgezeichnet (mit Makrorekorder) um das nicht alles händisch erstellenzu müssen. :-)

3. Blatt Daten:
Ist in der Sub aktualisieren mit raus. Einfach nur beim Blattnamen prüfen, welcher nicht erfasst werden soll.


So hier der Code. Dropwowns im A1 bis A4 und Daten ausgenommen.

Const zelle = "$A$1:$A$4"
 Private Sub Workbook_NewSheet(ByVal Sh As Object)
  With Sh.Range(zelle).Validation
             .Delete
             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=auswahlerstellen
             .IgnoreBlank = True
             .InCellDropdown = True
             .InputTitle = ""
             .ErrorTitle = ""
             .InputMessage = ""
             .ErrorMessage = ""
             .ShowInput = True
             .ShowError = True
         End With
 End Sub
 
 
 
 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Not Intersect(Target, Sh.Range(zelle)) Is Nothing Then
     For Each blatt In Worksheets
         If blatt.Name <> "Daten" Then
              With blatt.Range(zelle).Validation
                 .Delete
                 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:=auswahlerstellen
                 .IgnoreBlank = True
                 .InCellDropdown = True
                 .InputTitle = ""
                 .ErrorTitle = ""
                 .InputMessage = ""
                 .ErrorMessage = ""
                 .ShowInput = True
                 .ShowError = True
             End With
         End If
     Next
 End If
 End Sub
 
 Function auswahlerstellen() As String
 Dim blatt As Worksheet
 Dim ende As Long, zeile As Long
 Dim aktauswahl
 Dim position
 
 ende = Worksheets("Daten").Cells(Worksheets("Daten").Rows.Count, 1).End(xlUp).Row
 
 aktauswahl = Worksheets("Daten").Range("A2:A" & ende)
 
 For Each blatt In Worksheets
     If blatt.Name <> "Daten" Then
         For zeile = 1 To ende - 1
             For Each position In blatt.Range(zelle)
                 If aktauswahl(zeile, 1) = position Then
                     aktauswahl(zeile, 1) = ""
                     Exit For
                 End If
             Next
         Next
     End If
 Next
 
 For zeile = 1 To ende - 1
     If aktauswahl(zeile, 1) <> "" Then auswahlerstellen = auswahlerstellen & aktauswahl(zeile,  _
 1) & ","
 Next
 
 End Function

VG

  

Betrifft: DANKE von: 1713450.html
Geschrieben am: 17.09.2019 12:08:56

Lieber Matthias,

ich danke dir für deine Geduld und umfangreiche Hilfe.

Ich habe nun viel gelernt und noch mehr das ich aufarbeiten kann.

Damit werde ich mich nun erst einmal eine Weile beschäftigen.

Liebe Grüße

Steve

Beiträge aus dem Excel-Forum zum Thema "dynamisches Dropdown"