Make Excel List to multiselect

| | Allgemein, Programmierung, Windows

With this VBA Code you can make a Data Validation list to an multiselect list

Private Sub Worksheet_Change(ByVal Target As Range)
    '** Multiselect via Dropdown List (Validation)
    '** Insert in the code container of the relevant worksheet
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim makeMultiselect As Boolean
    Dim cellsToCheck As Range
    Dim arrValues As Variant
    Dim i As Integer
    Dim found As Boolean
    Dim result As String

    '** Error handling
    On Error GoTo Errorhandling

    '** Defining the range of cells that should allow multiselect
    Set cellsToCheck = Union(Range("E21"), Range("F20"), Range("F19"))

    '** Check if the changed cell is in the defined multiselect cells range
    If Not Application.Intersect(Target, cellsToCheck) Is Nothing Then
        makeMultiselect = True
    End If

    '** Perform multiselect in the defined range
    If makeMultiselect Then
        '** Define the range
        Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
        If rngDV Is Nothing Then GoTo Errorhandling
        '** Check if a valid cell was selected and insert values
        If Not Application.Intersect(Target, rngDV) Is Nothing Then
            Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
            '** Toggle logic for selecting/deselecting values
            If newVal = "" Then  ' Clearing the cell completely if Del is pressed
                Target.Value = ""
            ElseIf oldVal <> "" Then
                arrValues = Split(oldVal, ", ")
                found = False
                result = ""
                For i = LBound(arrValues) To UBound(arrValues)
                    If arrValues(i) = newVal And Not found Then
                        found = True
                    Else
                        If result = "" Then
                            result = arrValues(i)
                        Else
                            result = result & ", " & arrValues(i)
                        End If
                    End If
                Next i
                If Not found Then
                    If result = "" Then
                        result = newVal
                    Else
                        result = result & ", " & newVal
                    End If
                End If
                Target.Value = result
            End If
        End If
        Application.EnableEvents = True
    End If

Errorhandling:
    Application.EnableEvents = True
End Sub


Neueste Beiträge

Introducing a simple Docker Compose setup for HMS MQTT Publisher

Update: I am using this „much easier“ integration now: https://github.com/suaveolent/ha-hoymiles-wifi[GitHub, External]

I’ve been tinkering with my home automation setup recently, specifically integrating HMS-XXXXW-2T series micro-inverters with my system. The hms-mqtt-publisher[GitHub, External] is a neat tool that does just that by fetching telemetry info from these inverters and publishing it to an MQTT broker. The catch? It typically requires compiling from source with Cargo, Rust’s package manager.


Weiter >>

Effortlessly Backing Up Paperless-ngx with Cloudflare, rclone, and Docker

In the digital age, data backup is a non-negotiable part of managing any document management system. For those of us relying on the efficiency and organizational prowess of Paperless-ngx, ensuring our data is safe and recoverable is paramount. I’ve devised a seamless backup solution that utilizes the power of Cloudflare’s rclone and Docker, guaranteeing peace of mind and data security. Here’s a detailed look into my approach, which is applicable not just for Paperless-ngx but for any data stored on R2 storage. (https://www.cloudflare.com/developer-platform/r2/, Pricing: https://www.cloudflare.com/plans/developer-platform/ (10GB/Month for free))


Weiter >>

Ein Leitfaden für Senioren: Günstige Smartphones und Handys für Senioren bis 170 Euro

Mit über 80 Jahren beschlossen mein Opa, sich der digitalen Welt anzuschließen. Meine Oma nutzt bereits ein Xiaomi-Handy und schätzt dessen Einfachheit. Nun möchte mein Opa auch ein Mobiltelefon, das eine gute Kamera hat und einfach zu bedienen ist, aber dennoch WhatsApp und andere Funktionen unterstützt – und besonders wichtig: Kein Senioren Handy

Kurzversion: Am Ende ist es das Xiaomi Redmi 12 geworden, was zu dem Zeitpunkt bei Amazon für 149,90 Euro verfügbar war*


Weiter >>