| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple choice data validation
hi gurus -
i want to have a data validation where you can select multiple items from a drop-down list, where the multiple choices compile in the one cell. from someone previously asking the same thing, I went to Debra Dalgleish's great site & found a sample worksheet about just that, per Max's suggestion: "Try Debra's sample file at: http://www.contextures.com/excelfiles.html Under Data Validation, look for: DV0017 - Select Multiple Items from Dropdown List" I pasted the code from that tab into the code view of my own tab. I only had to tweak Debra's coding a little bit to get it to work for my purposes, including adding multiple target columns, which I needed. I had this working just fine, but then when I sent the xls to a co-worker who is the one needing to use it, it didn't work. So I sent it to another co-worker with whom I would try to troubleshoot it, and strangely it then worked for the 2nd co-worker, but not for me. So I retraced my steps and would try to troubleshoot my own xls. But when I went back and downloaded Debra's sample file again, now HERS doesn't even work for me! What could I be doing wrong? I have macros enabled (in fact security set to low so i don't have to). Are there any other settings that would make this not work? I haven't changed anything that I know of since the file was working for me (my own) just a few days ago. Very strange... Thanks!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiple choice data validation
oh - in case it helps, here is the code currently on my tab with the
validations. I don't know much about code, but only tinkered with it a little. It was working fine until just today. Anyone see anything in this that would make it unstable? here's the code (from 'view code' after right-clicking the sheet tab): 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 = 8 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 9 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 12 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 13 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 14 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 15 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 16 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiple choice data validation
Nothing jumps out, but a) simplify it (see below), and b) put a break in to
see whatr happens. 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 = 8 And Target.Column <= 16 Then If oldVal < "" And newVal < "" Then Target.Value = oldVal & ";" & newVal End If End If End If exitHandler: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "creativeops" wrote in message ... oh - in case it helps, here is the code currently on my tab with the validations. I don't know much about code, but only tinkered with it a little. It was working fine until just today. Anyone see anything in this that would make it unstable? here's the code (from 'view code' after right-clicking the sheet tab): 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 = 8 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 9 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 12 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 13 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 14 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 15 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 16 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiple choice data validation
Thanks Bob
But what do you mean by "put a break in"? "Bob Phillips" wrote: Nothing jumps out, but a) simplify it (see below), and b) put a break in to see whatr happens. 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 = 8 And Target.Column <= 16 Then If oldVal < "" And newVal < "" Then Target.Value = oldVal & ";" & newVal End If End If End If exitHandler: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "creativeops" wrote in message ... oh - in case it helps, here is the code currently on my tab with the validations. I don't know much about code, but only tinkered with it a little. It was working fine until just today. Anyone see anything in this that would make it unstable? here's the code (from 'view code' after right-clicking the sheet tab): 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 = 8 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 9 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 12 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 13 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 14 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 15 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 16 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiple choice data validation
Well - it's working again now! Thanks Bob!
Hopefully this time it will stay working.... "Bob Phillips" wrote: Nothing jumps out, but a) simplify it (see below), and b) put a break in to see whatr happens. 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 = 8 And Target.Column <= 16 Then If oldVal < "" And newVal < "" Then Target.Value = oldVal & ";" & newVal End If End If End If exitHandler: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "creativeops" wrote in message ... oh - in case it helps, here is the code currently on my tab with the validations. I don't know much about code, but only tinkered with it a little. It was working fine until just today. Anyone see anything in this that would make it unstable? here's the code (from 'view code' after right-clicking the sheet tab): 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 = 8 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 9 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 12 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 13 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 14 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 15 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 16 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiple choice data validation
I mean put a break in a line of code, select it in the VBIDE and F8(. Then
when it is run, it will stop and you can step through, F8, and see what happens. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "creativeops" wrote in message ... Thanks Bob But what do you mean by "put a break in"? "Bob Phillips" wrote: Nothing jumps out, but a) simplify it (see below), and b) put a break in to see whatr happens. 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 = 8 And Target.Column <= 16 Then If oldVal < "" And newVal < "" Then Target.Value = oldVal & ";" & newVal End If End If End If exitHandler: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "creativeops" wrote in message ... oh - in case it helps, here is the code currently on my tab with the validations. I don't know much about code, but only tinkered with it a little. It was working fine until just today. Anyone see anything in this that would make it unstable? here's the code (from 'view code' after right-clicking the sheet tab): 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 = 8 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 9 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 12 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 13 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 14 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 15 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ";" & newVal End If End If End If If Target.Column = 16 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to run when validation choice selected | Excel Discussion (Misc queries) | |||
| in a data validation list how do i color a choice? | Excel Discussion (Misc queries) | |||
| Multiple choice with values | Excel Discussion (Misc queries) | |||
| Data Validation (Multiple Choice) | Excel Worksheet Functions | |||
| Data Validation: Choice list does not appear | Setting up and Configuration of Excel | |||
Linear Mode
