• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Muliple selection drop down box

S

soul1974

New Member
Hi,

Could someone assist me in modifying the code below so that it uses a line break instead of a comma delimited, I want to insert this into different cells, not just one specific one.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
'  Edited to allow deselection of item (courtesy of Jamie Counsell)
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$A$14" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        GoTo Exitsub
    ElseIf Target.Value = "" Then
        GoTo Exitsub
    Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        Target.Value = Newvalue
       If Oldvalue <> "" Then
            If Newvalue <> "" Then
                If InStr(1, Oldvalue, ", " & Newvalue & ",") > 0 Then
                    Oldvalue = Replace(Oldvalue, Newvalue & ", ", "") ' If it's in the middle with comma
                    Target.Value = Oldvalue
                    GoTo jumpOut
                End If
                If Left(Oldvalue, Len(Newvalue & ", ")) = Newvalue & ", " Then
                    Oldvalue = Replace(Oldvalue, Newvalue & ", ", "") ' If it's at the start with comma
                    Target.Value = Oldvalue
                    GoTo jumpOut
                End If
                If Right(Oldvalue, Len(", " & Newvalue)) = ", " & Newvalue Then
                    Oldvalue = Left(Oldvalue, Len(Oldvalue) - Len(", " & Newvalue)) ' If it's at the end with a comma in front of it
                    Target.Value = Oldvalue
                    GoTo jumpOut
                End If
                If Oldvalue = Newvalue Then ' If it is the only item in string
                    Oldvalue = ""
                    Target.Value = Oldvalue
                    GoTo jumpOut
                End If
                Target.Value = Oldvalue & ", " & Newvalue
            End If
jumpOut:
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
 

soul1974​


These rules are for You too:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
vletm said:

soul1974​


These rules are for You too:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Click to expand...
Hi Vletm,
Noted, and it won't happen again.
 

soul1974​

You've already noted before using Cross-Posting.
You asked ...
If You would like to get replies then
# follow rules
# learn wait as written in rules
# You should 'sell' Your thread - make it interesting ... add more details
... eg How Your given code work now? ... and ... how to verify - how do it work after any modifications?
 
The solution from Jdelano at vbexpress.

New to VBA and require assistance modifying code.

Hi, Could someone assist me in modifying the code below so that it uses a line break instead of a comma delimited, I want to insert this into different cells, not just one specific one. Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com...
www.vbaexpress.com

This is straight replacement of comma with the keyword used in VBA for a line feed vbCrLf. Also, when posting code, used the # button, then paste your code between the two bracketed code markers.

Also tweaked the formatting a bit to make it easier to read.

What are you using this code for, given a line feed is much different than a comma there is likely a better way of doing whatever string dance you need.

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
' Edited to allow deselection of item (courtesy of Jamie Counsell)

Application.EnableEvents = True

On Error GoTo Exitsub

If Target.Address = "$A$14" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

GoTo Exitsub
ElseIf Target.Value = "" Then
GoTo Exitsub
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
Target.Value = Newvalue

If Oldvalue <> "" Then
If Newvalue <> "" Then
If InStr(1, Oldvalue, vbCrLf & Newvalue & vbCrLf) > 0 Then
Oldvalue = Replace(Oldvalue, Newvalue & vbCrLf, "") ' If it's in the middle with comma
Target.Value = Oldvalue
GoTo jumpOut
End If

If Left(Oldvalue, Len(Newvalue & vbCrLf)) = Newvalue & vbCrlf Then
Oldvalue = Replace(Oldvalue, Newvalue & vbCrLf, "") ' If it's at the start with comma
Target.Value = Oldvalue
GoTo jumpOut
End If

If Right(Oldvalue, Len(vbCrLf & Newvalue)) = vbCrLf & Newvalue Then
Oldvalue = Left(Oldvalue, Len(Oldvalue) - Len(vbCrLf & Newvalue)) ' If it's at the end with a comma in front of it
Target.Value = Oldvalue
GoTo jumpOut
End If

If Oldvalue = Newvalue Then ' If it is the only item in string
Oldvalue = ""
Target.Value = Oldvalue
GoTo jumpOut
End If

Target.Value = Oldvalue & vbCrLf & Newvalue
End If
jumpOut:
End If
End If
End If

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Click to expand...

vletm said:

soul1974​

Do You skip that third step from rules too?
Click to expand...
Please delete my profile and content
 
You must log in or register to reply here.
Back
Top