Completly Lost with Data Validation Code

I am trying to write a small script which will allow for multiple choices to be selected from a dropdown list, but for ONLY the dropdown list to accept input (ie data protection). I had asked for help before, and the nice people at Contextures sent me to their site to download one of their spreadsheets (dv0017 http://www.contextures.com/excelfiles.html).
I have been looking at the code, and I am clearly in over my head.
The code is below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
'I think this means that if there is more than one cell selected, then exit
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
'Absolutely no clue here. I have read a few different web pages try to explain both the SpecialCells function and the xlCellType Enums and I just get more and more confused.
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
'This code here inserts the new values. However, I still don't see where the values come from.
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

I've been scratching my head on this for a couple of days now. Any help would be greatly appreciated.

Answer this question

Completly Lost with Data Validation Code

bn.srinivasa rao
I see what you mean by not understanding where the value came from

newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal

this is what is happening, user changed a value from 5 to 10

store the newValue, stored in Target.Value = 10
undo the user change, Target.Value becomes 5.
store oldValue, stored in Target.Value = 5
and then restore Target.Value back to the original 10.
Okay, but why the flips Also, the values in this example are from a drop-down list.
perstam

Hi,

The only reason I can think of is it must have the only way to get the previously stored value before the user change. If values are coming from a drop down list then I don't know why.
Mr Pro Tools
Hi,

Cells.SpecialCells selects a range of cells that have a common value or type,

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

This selects all range of cells that contain validation criteria. A validation criteria is specified in a cell like this... cell e5 can only contain whole numbers. Have a look at the Validation object in the help files.

With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Minimum:="5", Maximum:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

I see what you mean by not understanding where the value came from

newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal

this is what is happening, user changed a value from 5 to 10
- store the newValue, stored in Target.Value = 10
- undo the user change, Target.Value becomes 5.
- store oldValue, stored in Target.Value = 5
- and then restore Target.Value back to the original 10.
Completly Lost with Data Validation Code

Answer this question