Results 1 to 3 of 3

Thread: VBA Code for Multiple Selections for a Drop Down - Error

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2022
    Posts
    1

    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.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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
  •  



Click Here to Expand Forum to Full Width