Multi-select picklist using Data Validation

**prgates** · 08-01-2012, 03:03 PM

I need to create a multi-value picklist in my spreadsheet. I read a previous post and copied/pasted the VB code into my spreadsheet, but I cannot get it to work. Thanks in advance for any assistance. Spreadsheet attached.

**rylo** · 08-01-2012, 08:09 PM

HI

Worked for me. What exactly are you doing?

rylo

**prgates** · 08-01-2012, 09:36 PM

Hi,

Found out it was working for me as well. Here's the hook...it only works on one column/spreadsheet. I have three picklist columns in my spreadsheet -- C, D, and E -- so when I was testing, I was clicking in column D which is the one I want to use a multi-select option in and it wouldn't work. I just happened to try column C and it worked! Lesson learned: if you have more than one picklist column, you can only make one multi-select and it has to be the first picklist column using Data Validation.

Thanks for responding.

**Leith Ross** · 08-01-2012, 10:08 PM

Hello prgates,

Use this version of the macro. This has been updated to prevent duplicates in the list.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngDV As Range
    Dim oldVal As Variant
    Dim newVal As Variant

        On Error GoTo exitHandler
        
        If Target.Count > 1 Then GoTo exitHandler
        
        Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
        
        Application.EnableEvents = False
        
            If Not Intersect(Target, rngDV) Is Nothing Then
            
                newVal = Target.Value
            
                Application.Undo
                oldVal = Target.Value
            
              ' Clear the cell is new value is "".
                If IsEmpty(newVal) Then
                    Target.Value = newVal
                    GoTo exitHandler
                End If
            
              ' Do not duplicate a selection.
                Select Case InStr(1, oldVal, newVal)
                    Case 0
                        If IsEmpty(oldVal) Then
                            Target.Value = newVal
                        Else
                            Target.Value = oldVal & "," & newVal
                        End If
                    Case Is > 0
                        Target.Value = oldVal
                End Select
            
            End If

exitHandler:
        Application.EnableEvents = True
  
End Sub

**prgates** · 08-01-2012, 10:13 PM

Thanks so much!

**Leith Ross** · 08-01-2012, 10:14 PM

Hello prgates,

You're welcome.

**prgates** · 08-01-2012, 10:19 PM

Question...

How do I marked my posted "Solved"? I tried typing SOLVED as a reply and it told me my text was too short. Should I just edit the post and typed SOLVED?

**Leith Ross** · 08-01-2012, 10:20 PM

Hello prgates,

Here is how...

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Qdogsmom** · 08-08-2012, 02:31 PM

Hi! I am trying to create a multi-select pick list and came across this post. I'm not exactly sure what to do with the macro posted above. Can someone explain. I am relativley new to creating lists!

I greatly appreciate any help you can provide!! Thank you!!

**prgates** · 08-08-2012, 03:54 PM

Right-click on the spreadsheet tab
Select View Code
Paste the VB code in the text box
SAVE and test

**Qdogsmom** · 08-09-2012, 10:09 AM

Got it!! Thank you prgates!!

**Cutter** · 08-09-2012, 01:19 PM

Hello Qdogsmom, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and keep it in mind for future posts. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**consultpc** · 03-16-2013, 01:24 PM

I solved the data validation multiple select issue by creating a small form that I can use to display the values the users need to select.
This method presents other advantages, such as: you can use it on virtually any number of columns, you are able to control how it behaves, allowing you to set the selection as multiple or single, it can also be used to allow users to input free text, and it provides a way for the admin to pass some information, guidance, etc to the users. It also presents the advantage of not being overwritten by users when copy-paste occurs, a problem for the classic data validation feature in Excel.
data_validation_form.png
The example was created in Excel 2010 and I tested it on a 2003 version with the Office Compatibility add-in installed, without getting any errors.
Please find attached the example and detailed explanations of how to use it.
P.S.
I only posted this solution because the ones that I came across didn't solve the problem for more than one column and I thought it might help some people out.
DataValidationForm.xlsm
DataValidationForm_howto.docx

Multi-select picklist using Data Validation

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Re: Multi-select picklist using Data Validation

Even though the thread is old, I would like to share with you my solution

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions