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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 08:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com