Multiple selection drop down menu

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
you are talking about drop down list in a VBA form? Or data validation drop down list?
 

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115

ADVERTISEMENT

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

Well-known Member
Joined
Feb 24, 2013
Messages
946
why dont you post a sample file in dropbox or skydrive, so I can have a look at it
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top