☰

So ändern Sie den Wert in der Excel-Dropdown-Liste

tag-icon • tag-icon microsoft-excel
So ändern Sie den Wert in der Excel-Dropdown-Liste

Ich habe eine Spalte, die Werte aus einem vordefinierten Satz (einer Dropdown-Liste) verwendet, implementiert mitDatenvalidierungen. Jetztwenn ich einen Wert in der Liste ändere, wird das Dropdown-Menü (wenn geöffnet) sofort diesen neuen Wert anbieten.Die Tabelle wird sich nicht automatisch aktualisieren, was bedeutet, dass einige der Werte in der Spalte ungültig sind, bis ich sie manuell korrigiere.

Ich habe Lösungen gesehen, die auf Makros/VBA basieren, aber gibt es nicht eine clevere Möglichkeit direkt in der Excel-Benutzeroberfläche?

Antwort1

Wie ich in den Kommentaren erwähnt habe, wäre dies ausschließlich mit VBA möglich.

Hier ist eine Option. Ich habe im gesamten Code Kommentare hinzugefügt. Dies setzt voraus, dass Sie für die Validierungsliste einen benannten Bereich mit dem Namen „Liste“ verwenden und dass dieser sich auf demselben Blatt wie die zu validierenden Zellen befindet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

Sie können auch dieBeispieltabelleIch habe es zusammengestellt, um das zu testen. (Enthält Makros!)

Antwort2

aber gibt es nicht eine clevere Möglichkeit direkt in der Excel-Benutzeroberfläche?

Ich glaube, ich kenne eines - zumindest scheint es alle deine Wünsche zu erfüllen:

  1. Sie müssen festlegendynamisch ändernde benannte Regionals Quelle für die Datenüberprüfung. Dies kann mithilfe OFFSETeiner Funktion erreicht werden. Angenommen, Sie haben die Werteliste für das Dropdown-Feld in Spalte A von Sheet1(Name ist nur für die Formel wichtig), die Zelle A1hat eine Überschrift List of values, z. B. , und die Werte werden am Anfang und darunter platziert A2, sollten Sie Folgendes tun: Gehen Sie zum Menüband Formulas > Name Manager, erstellen Sie eine neue Region (nennen wir sie Items) und legen Sie deren Region so fest, dass sie NICHT auf die Zellen zeigt, sondern verwenden Sie stattdessen die folgende Formel: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1).
  2. SatzDatenvalidierungRegeln: Anstatt auf einen Zellbereich für die Liste zu verweisen, geben Sie „ =Items-“ ein. Daher wird Ihr benannter Bereich als Quelle für die Listenelemente verwendet.
  3. Als Ergebnis des oben Gesagten erhalten Sie eine wirklich dynamische Liste, die alle Ihre Anforderungen erfüllt: Sie können Elemente in Spalte A frei ändern/hinzufügen, und diese Änderungen werden bei der nächsten Verwendung sofort in der Dropdown-Liste angezeigt. Gleichzeitig bleiben alte Werte erhalten.

Ich verwende diese Lösung seit etwa 2 Jahren bei meiner Arbeit. Ich hoffe, Sie finden sie auch nützlich!

PS, hier ist die eigentliche Beispieldatei:Dynamisches Dropdown

verwandte Informationen