-
Oct 13th, 2022, 03:56 PM
#1
Thread Starter
New Member
VBA Code for Multiple Selections for a Drop Down - Error
Hello -
First time posting here. I accidently managed to find the code I need to select multiple items in a dropdown menu. This is for multiple columns with the same dropdown items in each column. I was able to get my Column "F" and "I" to work. However I also need Column's L, O and R to do the same thing. When I try to add it, it basically stops as if it has run out of room. I've tried adding another line and all sorts of things and just don't know. It begins on line 8 of the code below that starts If Target.Address.
Can anyone help me with this?
Edna
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)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$F$5" Or Target.Address = "$F$6" Or Target.Address = "$F$7" Or Target.Address = "$F$8" Or Target.Address = "$F$9" Or Target.Address = "$F$10" Or Target.Address = "$F$11" Or Target.Address = "$F$12" Or Target.Address = "$F$13" Or Target.Address = "$F$14" Or Target.Address = "$F$15" Or Target.Address = "$F$16" Or Target.Address = "$F$17" Or Target.Address = "$F$18" Or Target.Address = "$F$19" Or Target.Address = "$F$20" Or Target.Address = "$F$21" Or Target.Address = "$F$22" Or Target.Address = "$I$5" Or Target.Address = "$I$6" Or Target.Address = "$I$7" Or Target.Address = "$I$8" Or Target.Address = "$I$9" Or Target.Address = "$I$10" Or Target.Address = "$I$11" Or Target.Address = "$I$12" Or Target.Address = "$I$13" Or Target.Address = "$I$14" Or Target.Address = "$I$15" Or Target.Address = "$I$16" Or Target.Address = "$I$17" Or Target.Address = "$I$18" Or Target.Address = "$I$19" Or Target.Address = "$I$20" Or Target.Address = "$I$21" Or Target.Address = "$I$22" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Last edited by Shaggy Hiker; Oct 17th, 2022 at 05:33 PM.
-
Oct 17th, 2022, 05:36 PM
#2
Re: VBA Code for Multiple Selections for a Drop Down - Error
Welcome to the forum, but a couple tips:
1) The Forum Test Area is just for trying out features, not for questions, as people generally won't see those.
2) You can make code look better by using either the [CODE][/CODE] tags, which are the # button, or the VB button. I prefer the #, but you can take your pick. In fact, you could try them both out in the Forum Test Area, as that's what it's for. Either press the button first and paste code between the tags, or paste in the code, highlight it, and press the button.
My usual boring signature: Nothing
-
Oct 18th, 2022, 12:39 PM
#3
Re: VBA Code for Multiple Selections for a Drop Down - Error
Code:
If (Target.Column = 6 Or Target.Column = 9) And (Target.Row >= 5 And Target.Row <= 22) Then
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|