Microsoft Excel

Herbers Excel/VBA-Archiv

VBA Dropdown Menu aus Worksheetnamen

Betrifft: VBA Dropdown Menu aus Worksheetnamen von: Dan
Geschrieben am: 01.09.2020 15:42:22

Servus zusammen,

kennt jemand eine Möglichkeit, einen Dropdown in einer Zelle - wie bspw. aus Datentools / Datenüberprüfung / Liste - mittels VBA zu erstellen, indem als Dropdown-Optionen immer die aktuellen Worksheets des Workbooks herausgegeben werden? Bin bisher auf meiner Suche nicht fündig geworden.

Grüße
Dan

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Oberschlumpf
Geschrieben am: 01.09.2020 15:57:26

Hi Dan,

hier, eine Datei zur Veranschaulichung der Lösung deines Problems:
https://www.herber.de/bbs/user/139971.xlsm

Nach jedem Öffnen der DAtei wird mein Makro gestartet.
Füge mal der Datei ein oder weitere Tabellenblätter hinzu, benenne diese vom Namen her gern um, speicher und schließ die Datei und öffne sie wieder.

Klick wieder auf A1 und öffne das Dropdown.
(die Farbe für A1 ist nur optional + kann auch weggelassen werden)

Hilfts?

Ciao
Thorsten

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Dan
Geschrieben am: 01.09.2020 16:03:25

Vielen Dank für deine Antwort.

Grundsätzlich super, nur möchte ich nicht jedes mal das Workbook speichern, schließen und öffnen, wenn ich möchte, dass mein Dropdown aktualisiert wird.

Außerdem, kann man in dem Dropdown die Dropdown-Option "Sonstiges" hinzufügen, welche nicht als Worksheet vorhanden ist?

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Oberschlumpf
Geschrieben am: 01.09.2020 16:12:33

Hi Dan,

dann lass das Makro sbAllSh einfach dann starten, wann es dir beliebt - du hast ja nicht! verraten, wie viele Andere auch...seuffzz...WANN du es aktualisiert haben möchtest :-/

Und das Hinzufügen von Dropdown-Option "Sonstiges" war auch nicht Bestandteil deiner 1. Frage!

Änder den Code
lstrAllSh = Left(lstrAllSh, Len(lstrAllSh) - 1)

um in
lstrAllSh = lstrAllSh & "Sonstiges"
Hilfts?

Ciao
Thorsten

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Dan
Geschrieben am: 01.09.2020 16:16:53

Hi Thorsten,

Hilft. Danke, dass du geantwortet hast, obwohl meine Fragen & deren Ausführungen dich genervt haben.

Gruß
Dan

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Oberschlumpf
Geschrieben am: 01.09.2020 16:31:49

Hi Dan,

mich haben nicht deine Fragen & deren Ausführungen genervt, sondern die Tatsache, das du in der Erstfrage - nicht gleich all das - erwähnt hattest, was du auch gern gelöst bekommen wolltest.

Hättest du alles in der 1. Frage erwähnt, hätte meine 1. Antwort auch gleich all deine Fragen beantwortet.

n kleiner Tipp von mir:
bitte immer genau überlegen, was man erreichen möchte. so wird es dann weniger Nachfragen geben
(kannst du beachten, musst du aber natürlich nicht - du entscheidest!)

Ciao
Thorsten

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Dan
Geschrieben am: 01.09.2020 16:45:19

Hi Thorsten,

klar, da hast du völlig recht. Nur manchmal kommen im Nachhinein kurzfristig noch Ideen bzw. Fragen auf und da hier die meisten so hilfsbereit sind, hau ich diese dann auch gleich raus. Genauso wie jetzt nochmal:

Ich spiele gerade mit deiner VBA und habe sie in meinem Excel Workbook eingepflegt, dabei ist mir aufgefallen, dass ich eine xlVeryHidden Sheet habe und dieses mir nun auch im Throsten-VBA-Dropdown angezeigt wird. Habe versucht es mit & <> xlVeryHidden auszuklammern, aber das funktioniert nicht.

Würde mich über eine hoffentlich letzte Beantwortung meiner Frage hierzu freuen - kannst du machen, musst du aber natürlich nicht - du entscheidest ;)

Gruß
Daniel

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Oberschlumpf
Geschrieben am: 01.09.2020 16:55:05

Hi Dan,

ja, ich weiß, es können immer Ideen kommen, die einem erst später einfallen.
Mir geht es halt nur darum, dass man vor dem Fragen - alle Fakten - zur aktuellen Frage stellt.

Ok, deine neue Idee:

änder diesen Code
lstrAllSh = lstrAllSh & Sheets(liSh).Name & ","

um in
If Sheets(liSh).Visible = True Then
    lstrAllSh = lstrAllSh & Sheets(liSh).Name & ","
End If

So werden nur die sichtbaren Tabellen im DropDown gezeigt.

Hilfts?

Ciao
Thorsten

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Dan
Geschrieben am: 01.09.2020 17:23:29

Ich kann Gerd nur zustimmen. Vielen Dank für deine Hilfe und einen schönen Abend!

Betrifft: AW: VBA Dropdown Menu aus Worksheetnamen
von: Gerd L
Geschrieben am: 01.09.2020 16:57:35

Moin,

ein schönes Teil!
Private Sub Worksheet_Activate()
    Call sbbAllSh
End Sub

Sub sbAllSh()

    Dim liSh As Integer, lstrAllSh As String
    
        'in der Variablen lstrAllSh werden die Namen aller vorhandenen Tabellenblätter,  _
getrennt durch Komma, gesammelt
        For liSh = 1 To ThisWorkbook.Sheets.Count
            If ThisWorkbook.Sheets(liSh).Visible = True Then
                lstrAllSh = lstrAllSh & ThisWorkbook.Sheets(liSh).Name & ","
            End If
        Next
    
        lstrAllSh = lstrAllSh & "Sonstiges"

        'in Tabelle1 wird nun der Zelle A1 per Datengültigkeitsliste der Inhalt aus lstrAllSh  _
zugewiesen
        With ThisWorkbook.Sheets(1).Range("A1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=lstrAllSh
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

End Sub

Gruß Gerd

Betrifft: Danke! :-) ...owT
von: Oberschlumpf
Geschrieben am: 01.09.2020 16:59:34



Beiträge aus dem Excel-Forum zum Thema "VBA Dropdown Menu aus Worksheetnamen"