Unique Strings while selecting multiple Items from a Drop down in a Cell

hariready

New Member
Joined
Sep 11, 2014
Messages
1
Hi. I was trying to create a drop down list, where the user could select multiple options from the list, but still the options selected must be unique within a Cell. For Instance: if a Dropdown has A, B, C as values, selecting A and C is a valid selection, but selecting A, C and A is not.

I did manage to create a multi-select drop down, (which is a comma separated strings) but could not implement the unique string constraint. Any ideas
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
NB:- The validation list is set in "B1" , Alter to suit !!!!
NB:- This code enables multiselections from a Validation Drop down list.
In order to clear the selections I have added the word "Clear" in the Validation List to enable the List to be reset. (Please do the same)
Code:
Option Explicit
[COLOR="Navy"]Dim[/COLOR] Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "B1" [COLOR="Navy"]Then[/COLOR]
    Application.EnableEvents = False
    [COLOR="Navy"]If[/COLOR] Not Target = "Clear" And InStr(Str, Target) = 0 [COLOR="Navy"]Then[/COLOR]
       Str = Str & ", " & Target
            
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]If[/COLOR] Target = "Clear" [COLOR="Navy"]Then[/COLOR]
         Str = ""
    [COLOR="Navy"]End[/COLOR] If
Target = Mid(Str, 2)
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,819
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top