Multiple selection drop down menu

[IraAxa]

I know how to create drop down menu and multiple select them, but if i have one column/row that i want to have only one option to choose from drop down menu, instead of multiple, can i do it? Hence, i want to leave other cells as multiple selection on the same sheet.

 

[sergioMabres]

your question looks like a "filtered drop down select"
do you know that you can write just one value for list in data validation, that will give just one value to choose from
if your question is a question of filtered list there is here and in other places answers how to do that
for instance drop down menu - Excel filter from other list - Stack Overflow
Sergio

 

[IraAxa]

your question looks like a "filtered drop down select"
do you know that you can write just one value for list in data validation, that will give just one value to choose from
if your question is a question of filtered list there is here and in other places answers how to do that
for instance drop down menu - Excel filter from other list - Stack Overflow
Sergio

Thanks,

but i still want to have drop down menu with options to choose, instead of making 3 different rows for each - hence I'd like to have a rule of selecting just one, not multiple - but don't find the pop up message that attractive?!

thanks

 

[sergioMabres]

you are talking about drop down list in a VBA form? Or data validation drop down list?

 

[IraAxa]

you are talking about drop down list in a VBA form? Or data validation drop down list?

not the VBA - data validation - i don't really know how to use VBA

Thanks

 

[sergioMabres]

why dont you post a sample file in dropbox or skydrive, so I can have a look at it

 

[IraAxa]

why dont you post a sample file in dropbox or skydrive, so I can have a look at it

Thanks;

here is the link in Skydrive
https://skydrive.live.com/redir?resid=48C2CB43175DFD0B!177&authkey=!AEvtverK7hjqnTk

 

[IraAxa]

why dont you post a sample file in dropbox or skydrive, so I can have a look at it

I think i got it - i can just move the bottom ones to the 3rd cell, and code won't affect it

Thanks

 

[sergioMabres]

glad to help, just by listening sometimes it helps

 

[sergioMabres]

I had a peek on the file and your sheet has a macro that makes ALL dropdown multiples I adde some code (or Target.Row = 25) to avoid multiples selection on line 25

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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 Or Target.Row = 25 Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 2 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            If Right(oldVal, Len(newVal)) = newVal Then
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
            Else
                Target.Value = Replace(oldVal, newVal & ", ", "")
            End If
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Here your file modified
https://dl.dropboxusercontent.com/u/23094164/Test.changed.one.line.xlsm

You can now use your solution or the modified sheet
Sergio