Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default Selecting multiple items from a drop down list

I have a small drop down list with only 4 items. I want to be able to allow
the users to choose more than one of these items. I tried using this code
Option Explicit

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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
that I got from this site:
(http://www.contextures.com/excelfiles.html) (scroll down to Select Mulitple
Items from a drop down list).

However, after right clicking and pasting in the code, I still don't have
the option of choosing more than one option in my drop down list. I know
nothing about code, so I'd appreciate any help that can be delivered with
that constraint!

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Selecting multiple items from a drop down list

Jeremy

Is your drop-down list in Column C?

The code only works on DV dropdowns in Column C

If Target.Column = 3 Then


Gord Dibben MS Excel MVP

On Thu, 7 Dec 2006 10:21:01 -0800, Jeremy
wrote:

I have a small drop down list with only 4 items. I want to be able to allow
the users to choose more than one of these items. I tried using this code
Option Explicit

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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
that I got from this site:
(http://www.contextures.com/excelfiles.html) (scroll down to Select Mulitple
Items from a drop down list).

However, after right clicking and pasting in the code, I still don't have
the option of choosing more than one option in my drop down list. I know
nothing about code, so I'd appreciate any help that can be delivered with
that constraint!

Thanks.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default Selecting multiple items from a drop down list

Gord:

No, it's in Column F. I'm assuming that that means I should change the code
to

If Target.Column = 6 Then....

I tried that and it still doesn't work.

"Gord Dibben" wrote:

Jeremy

Is your drop-down list in Column C?

The code only works on DV dropdowns in Column C

If Target.Column = 3 Then


Gord Dibben MS Excel MVP

On Thu, 7 Dec 2006 10:21:01 -0800, Jeremy
wrote:

I have a small drop down list with only 4 items. I want to be able to allow
the users to choose more than one of these items. I tried using this code
Option Explicit

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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
that I got from this site:
(http://www.contextures.com/excelfiles.html) (scroll down to Select Mulitple
Items from a drop down list).

However, after right clicking and pasting in the code, I still don't have
the option of choosing more than one option in my drop down list. I know
nothing about code, so I'd appreciate any help that can be delivered with
that constraint!

Thanks.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Selecting multiple items from a drop down list

Jeremy

Works for me when I change to Column = 6 and the DV dropdowns are in Column F

Where have you stored the code?

It must go into a sheet module.

Right-click on the sheet tab and "View Code".

Copy/paste in that module.

I assume you downloaded Debra's sample workbook.

Have you tried on hers to move the DV dropdown cells to column F, delete from
column C and change the code to column = 6?

That is how I tested and works fine.


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 12:04:00 -0800, Jeremy
wrote:

Gord:

No, it's in Column F. I'm assuming that that means I should change the code
to

If Target.Column = 6 Then....

I tried that and it still doesn't work.

"Gord Dibben" wrote:

Jeremy

Is your drop-down list in Column C?

The code only works on DV dropdowns in Column C

If Target.Column = 3 Then


Gord Dibben MS Excel MVP

On Thu, 7 Dec 2006 10:21:01 -0800, Jeremy
wrote:

I have a small drop down list with only 4 items. I want to be able to allow
the users to choose more than one of these items. I tried using this code
Option Explicit

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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
that I got from this site:
(http://www.contextures.com/excelfiles.html) (scroll down to Select Mulitple
Items from a drop down list).

However, after right clicking and pasting in the code, I still don't have
the option of choosing more than one option in my drop down list. I know
nothing about code, so I'd appreciate any help that can be delivered with
that constraint!

Thanks.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default Selecting multiple items from a drop down list

Gord:

I'll try that and see if it works.

Thanks.

"Gord Dibben" wrote:

Jeremy

Works for me when I change to Column = 6 and the DV dropdowns are in Column F

Where have you stored the code?

It must go into a sheet module.

Right-click on the sheet tab and "View Code".

Copy/paste in that module.

I assume you downloaded Debra's sample workbook.

Have you tried on hers to move the DV dropdown cells to column F, delete from
column C and change the code to column = 6?

That is how I tested and works fine.


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 12:04:00 -0800, Jeremy
wrote:

Gord:

No, it's in Column F. I'm assuming that that means I should change the code
to

If Target.Column = 6 Then....

I tried that and it still doesn't work.

"Gord Dibben" wrote:

Jeremy

Is your drop-down list in Column C?

The code only works on DV dropdowns in Column C

If Target.Column = 3 Then


Gord Dibben MS Excel MVP

On Thu, 7 Dec 2006 10:21:01 -0800, Jeremy
wrote:

I have a small drop down list with only 4 items. I want to be able to allow
the users to choose more than one of these items. I tried using this code
Option Explicit

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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
that I got from this site:
(http://www.contextures.com/excelfiles.html) (scroll down to Select Mulitple
Items from a drop down list).

However, after right clicking and pasting in the code, I still don't have
the option of choosing more than one option in my drop down list. I know
nothing about code, so I'd appreciate any help that can be delivered with
that constraint!

Thanks.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want Vlookup to list multiple items with the same key? GarToms Excel Worksheet Functions 13 May 10th 06 11:21 PM
Hide previously used items from multiple drop lists ron Excel Worksheet Functions 0 February 27th 06 10:15 PM
Hide previously used items from multiple drop lists ron Excel Worksheet Functions 0 February 22nd 06 07:38 PM
How to add and delete items from a list box or drop down box? Fifee Excel Discussion (Misc queries) 3 August 12th 05 01:49 PM
changing value of a cell by selecting an item from a drop down list Bobby Mir Excel Worksheet Functions 6 June 8th 05 08:33 PM


All times are GMT +1. The time now is 09:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"