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

Getting Started with Alfresco SDK/Development: A Beginner’s Guide to Automating File Organization with Alfresco Behaviors

Alfresco is an enterprise content management platform known for its flexibility and extensibility. One powerful way to extend its functionality is through Behaviors, which allow you to run custom logic whenever specific repository events occur. For example, you can trigger custom actions whenever nodes are created, updated, or deleted.


Weiter >>

Enhancing Alfresco’s Public API (ACS): A Step-by-Step Guide to Custom Node Extensions with MIME Type Restrictions

Introduction

In today’s digital landscape, controlling and validating the types of content users can upload into systems is essential for security and data integrity. Alfresco, a leading open-source content services platform, offers a flexible public API that enables developers to create custom extensions and adapt the platform to specific organizational needs. This article provides a step-by-step guide to implementing a MIME type restriction feature in Alfresco’s Nodes API, allowing for more controlled and secure content uploads.


Weiter >>

Warum der Air Assist unverzichtbar ist – Mein Erfahrungsbericht

Nachdem ich meinen ATOMSTACK A12 Ultra Laser[*] und die R2 V2 Drehwalze[*] in Betrieb genommen hatte, war es nur eine Frage der Zeit, bis ich mir zusätzlich ein Air Assist System zugelegt habe. Ich entschied mich für das DEWALLIE Air Assist Set[*], und ich kann schon vorweg sagen: Es war eine der besten Ergänzungen für meine Lasergravur-Setups, vor allem beim Arbeiten mit Holz!


Weiter >>

Mein neues Setup: Der ATOMSTACK R2 V2 Drehwalze und A12 Ultra/Pro Laser – Perfekt für Gravuren auf runden Objekten!

Als ich mir kürzlich den ATOMSTACK A12 Ultra Laser[*] zugelegt habe, war mir schnell klar, dass ich das volle Potenzial dieses leistungsstarken Gravierers ausschöpfen wollte. Also habe ich nicht lange gezögert und gleich die ATOMSTACK R2 V2 Drehwalze[*] dazu gekauft, die es ermöglicht, zylindrische Objekte wie Trinkflaschen, Gläser oder Stifte zu gravieren.


Weiter >>