Assistance With Multi-Select Code

[KennyExcel]

Hi all,

Newbie here in need of some help.....

I have a cell which is a data validation dropdown that will eventually contain a lot of values. What I am trying to do is make this multi-select. I have already entered some code to make it multi-select but in the following format:

Value1, Value2, Value3

I would like the format to be:

Value 1
Value 2
Value 3

A bonus add on if possible would be if you select the same value for a second time, it will remove it from the selected list

Here is my current code (Bare in mind I am a newbie to this) I have inserted in the view code section of the tab:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then

Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then

Else
If newVal = "" Then

Else
Target.Value = oldVal _
& ", " & newVal

End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

Appreciate any help you can give

 

[James006]

Hi,

Take a look at Debra's excellent explanation ...

http://www.contextures.com/excel-data-validation-multiple.html

HTH

 

[KennyExcel]

Thanks James, I have read through this but it doesn't really do what I am looking for and from what I can see the code that would get close to what I am looking for requires a payment to be made. I was just looking for some assistance from like-minded folk so not really keen on paying for it.

Would really appreciate if anyone else could help me out, pretty stuck with this.

 

[KennyExcel]

Actually scratch that there is no paid option I just took it up wrong lol, reviewing now so hopefully this will help! Fingers crossed!

 

[James006]

Hi,

If you are looking for a cell splitter ...

Sub CellSplitter()
    Dim Temp As Variant
    Dim CText As String
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer
    Dim iColumn As Integer
    Dim lNumCols As Long
    Dim lNumRows As Long
    Dim wksSource As Worksheet
    Dim wksNew As Worksheet
    Dim iTargetRow As Integer
    
    iColumn = 1


    Set wksSource = ActiveSheet
    Set wksNew = Sheet2


    iTargetRow = 0
    With wksSource
       lNumCols = .Range("IV1").End(xlToLeft).Column
       lNumRows = .Range("A65536").End(xlUp).Row
        For J = 1 To lNumRows
            CText = .Cells(J, iColumn).Value
            Temp = Split(CText, ", ")
            For K = 0 To UBound(Temp)
                iTargetRow = iTargetRow + 1
                For L = 1 To lNumCols
                    If L <> iColumn Then
                        wksNew.Cells(iTargetRow, L) _
                          = .Cells(J, L)
                    Else
                        wksNew.Cells(iTargetRow, L) _
                          = Temp(K)
                    End If
                Next L
            Next K
        Next J
    End With
End Sub

HTH