Singleand Multiple Choice Drop Downs in same worksheet

J

jbeals

New Member
Joined
Mar 23, 2012
Messages
2
I'm learning this as a I go but I've finally figured out the correct code to allow multiple selections from one of my drop down lists. Now, when I try selecting an item from a separate list (which I only want to be able to choose one thing), it's accepting multiple items. How do I have both working properly?

This is the code I have for my multiple selection list (which is only in column M):

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 Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column >= 7 And Target.Column <= 16 Then
If oldVal <> "" And newVal <> "" Then
Target.Value = oldVal & "; " & newVal
End If
End If
End If
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi and welcome to the forum. Here's one way it could be done.

If you only have the one specific drop down list you want to have multiselect (cell M10 in this example), you could use something like this...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oldVal As String
    Dim newVal As String
    
    If Target.Address(0, 0) <> "[COLOR="Red"]M10[/COLOR]" Then GoTo exitHandler
    On Error Resume Next
    
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal

    If oldVal <> "" And newVal <> "" Then
        Target.Value = oldVal & "; " & newVal
    End If
 
Upvote 0
Re: Single and Multiple Choice Drop Downs in same worksheet

Thanks for your help before. Now, I need to have another column with multiple choices. What would the code be for only column O and S? Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String

If Target.Address(0, 0) >= "O6" And Target.Address <= O300 Then GoTo exitHandler
On Error Resume Next

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 15 Then
If oldVal <> "" And newVal <> "" Then
Target.Value = oldVal & "; " & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
 
Upvote 0
Hello, I am new to VB and want to have a multi select dropdown that will also allocate numeric dollar values for each selection. So I need 2 things, 1. the code for the multiselection (i think shown in this email previously), and 2. have to assign a numeric value to each selection. I then want to use the combined numeric dollar sum in the cell/multiselection in a separate calculation. Many thanks.

AlphaFrog said:
Hi and welcome to the forum. Here's one way it could be done.

If you only have the one specific drop down list you want to have multiselect (cell M10 in this example), you could use something like this...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oldVal As String
    Dim newVal As String
    
    If Target.Address(0, 0) <> "[COLOR=Red]M10[/COLOR]" Then GoTo exitHandler
    On Error Resume Next
    
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal

    If oldVal <> "" And newVal <> "" Then
        Target.Value = oldVal & "; " & newVal
    End If
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Select multiple values from the dropdown menu
Replies
2
Views
563
Akuini
Akuini
S
  • Question
Autofilter in VBA when criteria is has more words separated by a comma
Replies
0
Views
658
shafiey
S
K0st4din
  • Solved
How to activate two Option Explicit Private Sub Worksheet_Change(ByVal Target As Range)
Replies
3
Views
978
K0st4din
K0st4din
J
  • Solved
Listbox VBA for Multiple Selection in single cell separated by a comma - list not appearing
Replies
3
Views
1K
jessitarexcel
J
K
  • Solved
VBA multiple selections from a drop-down in protected sheet
Replies
2
Views
2K
kcmuppet
K

Forum statistics

Threads
1,214,988
Messages
6,122,620
Members
449,092
Latest member
amyap

Share this page

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
Back
Top