Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    choose more than one in dropdown list

    I would like users to have the option to choose more than one option from a dropdown list. I want to limit the cell, so they can only choose each item once (per cell).

  2. #2
    New Lounger
    Join Date
    Nov 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you need to right click on the sheet where you want this to happen. Then drop down the (general) tab to be worksheet. The dropdown list on the right should be change. If you remove the code currently existing and replace it with the following it should do what you want for that sheet. Change the red terxt field to the column number (normally a letter but in this case replace the alphabetical with a number a=1, b=2 etc.). Save and you have it.

    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
    'do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 9 Then
    If oldVal = "" Then
    'do nothing
    Else
    If newVal = "" Then
    'do nothing
    Else
    Target.Value = oldVal _
    & ", " & newVal
    End If
    End If
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •