Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want Vlookup to list multiple items with the same key? | Excel Worksheet Functions | |||
Hide previously used items from multiple drop lists | Excel Worksheet Functions | |||
Hide previously used items from multiple drop lists | Excel Worksheet Functions | |||
How to add and delete items from a list box or drop down box? | Excel Discussion (Misc queries) | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions |