Data Validation‎ > ‎

Multi-choice DV

SPECIFICATIONS:
    1. Data validation lists active in a range of cells, A1:A10 in this exercise
    2. User can select multiple items from DV list and each item should show


CODE:

These macros go into the Sheet Module

Public Prior As String

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire
'Date:      10/2/2009
'Summary:   These two macros create a multi-choice dropbox
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        If Target <> "" Then
            If Prior <> "" Then Target = Target.Text & "," & Prior
            Prior = Target
        Else
            Prior = ""
        End If
    End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Prior = Target.Text
End Sub



3. This version works like above except each option can be selected only once in the multiple selections, you could not get Cat, Cat, Cat

CODE

Option Explicit
Public Prior As String

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire
'Date:      10/2/2009, 8/17/2010
'Summary:   These two macros create a multi-choice dropbox
'           This version allows each option to be selected only once
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        If Target <> "" Then
            If Prior <> "" Then
                If InStr(Prior, Target) > 0 Then
                    Application.Undo
                Else
                    Target = Target.Text & "," & Prior
                    Prior = Target
                End If
            End If
        Else
            Prior = ""
        End If
    End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Prior = Target.Text
End Sub



4. This version works like above except each option can be selected only once in the multiple selections and each choice is numbered

CODE

Option Explicit
Public Prior As String

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire
'Date:      10/2/2009, 8/17/2010
'Summary:   These two macros create a multi-choice dropbox
'           This version allows each option to be selected only once
'           This version numbers each choice as it is made
Dim Cnt As Long
Application.EnableEvents = False

If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        If Target <> "" Then
            If InStr(Prior, Target) > 0 Then
                Application.Undo
            Else
                Cnt = Len(Prior) - Len(Replace(Prior, Chr(10), ""))
                If Prior = "" Then
                    Target = "1. " & Target
                Else
                    Target = Prior & Chr(10) & Cnt + 2 & ". " & Target
                End If
                Prior = Target
            End If
        Else
            Prior = ""
        End If
    End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Prior = Target.Text
End Sub




Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments