Multiple Data selection

[daryaabi]

Hello I used this code last week and it worked perfectly but now is not working. Can anyone see what I am doing wrong ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String, newVal As String



On Error GoTo exitHandler



'---Test to see if code should continue.
' Test statements could be combined, and are
' shown separately to simplify modification




If Target.Count > 1 Or Target.Text = "" Then Exit Sub



If Target.SpecialCells(xlCellTypeSameValidation) _
Is Nothing Then Exit Sub



'---modify to any valid range reference on activesheet
If Intersect(Target, Range("K:O")) _
Is Nothing Then Exit Sub



Application.EnableEvents = False
newVal = Target.Text
Application.Undo
oldVal = Target.Text
Target.Value = newVal
If oldVal = "" Then GoTo exitHandler



If oldVal = newVal Then
Target.Value = ""
ElseIf InStr(1, oldVal, newVal) > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.Value = Replace(oldVal, newVal & Chr(10), "")
End If
Else
Target.Value = oldVal & Chr(10) & newVal
End If



exitHandler:
Application.EnableEvents = True
End Sub


</pre>

 

[wigi]

Hello

I would turn off this line:

On Error GoTo exitHandler

and see what error is generated, if any.

It seems that you're new here.


Can you please use

 tags when you paste code on the forum?
Code tags format the code making it easier to read and hence follow the logic of the code.


You can use [CODE] tags in this way: 


Add the word [color=blue][b][code][/B][/color] before the first line of code, and
add the word [color=blue][b][/ code] (without space)[/B][/color] after the last line of code.


Or: you could use the "#" icon when changing / composing a message in the Advanced editing screen.


Thanks for your consideration.

 

[daryaabi]

Thank you for your reply

I copied all cells in a new excel worksheet and run the VB cods again and worked
but then when I changed some parts again the cods were not working

I want to have multiple selection for Courses and professors and also is there any way that I can treat these rows a s a one single row and when I enter have the same format again for my new entry ? the image is attached here

Best

 

[Jerry Sullivan]

Hi daryaabi,

It appears you are trying to adapt code from post #8 of this thread however later in post #13 of that thread, there were some improvements made to address some significant problems with the post #8 code. That might explain why you had inconsistent results.

http://www.mrexcel.com/forum/excel-questions/636094-multiple-items-data-validation-list.html

Try adapting the code in Post 13 to your setup making as few modifications as necessary to reference the correct range of cells.

Also please heed Wigi's advice and simple instructions on how to post VBA code using code tags so that it is more readable to others.
The image you pasted in your last post can't be seen by others (you might be able to see if it is referencing a file on your local computer).

 

[daryaabi]

Thank you for your respond

I don't know if you can see my excel from here: https://drive.google.com/file/d/0B3sPNkceuC0Za2ZtNjg2S2pYd28/edit?usp=sharing

plz let me know

It is very time consuming for me, I do all the data validation and the code is working but when I want to apply to all cells it takes forever and then it stop the Excel. I don't know if I should write a formula ? or is there any way I can treat these rows as a one row that in future with a tab we can have all of these row instead of a new single row

I will apply codes from #13

let me know thank you for supporting us as Excel dummies


Best

Hi daryaabi,

It appears you are trying to adapt code from post #8 of this thread however later in post #13 of that thread, there were some improvements made to address some significant problems with the post #8 code. That might explain why you had inconsistent results.

http://www.mrexcel.com/forum/excel-questions/636094-multiple-items-data-validation-list.html

Try adapting the code in Post 13 to your setup making as few modifications as necessary to reference the correct range of cells.

Also please heed Wigi's advice and simple instructions on how to post VBA code using code tags so that it is more readable to others.
The image you pasted in your last post can't be seen by others (you might be able to see if it is referencing a file on your local computer).

 

[daryaabi]

https://drive.google.com/file/d/0B3sPNkceuC0ZSmxTbWFvSzlGeU0/edit?usp=sharing

 

[daryaabi]

I am wondering if this is a right code I am using as it does nothing to my column L-AN

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sOld As String, sNew As String
    Dim sCrOldCr As String, sCrNewCr As String
    
    On Error GoTo exitHandler
    
    '---Test to see if code should continue.
    '   Test statements could be combined, and are
    '      shown separately to simplify modification


    If Target.Count > 1 Or Target.Text = "" Then Exit Sub
   
    If Target.SpecialCells(xlCellTypeSameValidation) _
        Is Nothing Then Exit Sub
  
    '---modify to any valid range reference on activesheet
    If Intersect(Target, Range("K:AN")) _
        Is Nothing Then Exit Sub


    Application.EnableEvents = False
    sNew = Target.Text
    Application.Undo
    sOld = Target.Text
    Target.Value = sNew
    If sOld = "" Then GoTo exitHandler
    
    '--wrap in delimiters to avoid partial match
    sCrNewCr = Chr(10) & sNew & Chr(10)
    sCrOldCr = Chr(10) & sOld & Chr(10)
    
    Select Case InStr(1, sCrOldCr, sCrNewCr)
        Case 0 '--no match
            Target = sOld & Chr(10) & sNew
        Case 1 '--match with first item
            Target = IIf(sOld = sNew, "", _
                Replace(Chr(10) & sOld, sCrNewCr, ""))
        Case Len(sOld) - Len(sNew) + 1 '--match with last item
            Target = Replace(sOld & Chr(10), sCrNewCr, "")
        Case Else  '--match with one middle item
            Target = Replace(sOld, sCrNewCr, Chr(10))
    End Select


exitHandler:
    Application.EnableEvents = True
End Sub

https://drive.google.com/file/d/0B3sPNkceuC0ZNlVNaDZjOWc2R3c/edit?usp=sharing

 

[Jerry Sullivan]

I tested your example file and it worked as intended.
Specifically, I tested BSBA!L4 and as each DV list item was selected it was either added or subtracted from the list.

One possible reason for your inconsistent results could be that you have Application.EnableEvents set to False.

Trying running this macro which will toggle EnableEvents on and off. This macro should be copied to a Standard Code Module (not a Sheet Code Module).

Sub ToggleEnableEvents()
    Application.EnableEvents = Not Application.EnableEvents
    MsgBox "EnableEvents is now set to: " & _
        Application.EnableEvents
End Sub

 

[daryaabi]

Thank you so much I will try it
best
Leila

I tested your example file and it worked as intended.
Specifically, I tested BSBA!L4 and as each DV list item was selected it was either added or subtracted from the list.

One possible reason for your inconsistent results could be that you have Application.EnableEvents set to False.

Trying running this macro which will toggle EnableEvents on and off. This macro should be copied to a Standard Code Module (not a Sheet Code Module).

Sub ToggleEnableEvents()
    Application.EnableEvents = Not Application.EnableEvents
    MsgBox "EnableEvents is now set to: " & _
        Application.EnableEvents
End Sub