Someone Explain To Me What This Code is Doing?

[SanFelippo]

Hello,

I am doing my best to learn and understand VBA coding, and this one particular piece of code I think I have an idea of what it is doing, but I would rather be absolutely sure so I know I understand it completely. Could someone explain what this is doing? It would be great if you could put comments in the code after some of the lines to explain what they are doing, as well as explain overall what the code is doing. Is it some way of making sure not more than one cell is selected or something like that?

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 = 24 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

Thanks!

 

[MrKowz]

Private Sub Worksheet_Change(ByVal Target As Range)
'Define variables
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String

'If the number of cells changed in one action is greater than 1, then go to the label "exitHandler"
If Target.Count > 1 Then GoTo exitHandler

'If an error occurs, continue the code on the next line
On Error Resume Next

'Sets the rngDV variable to be any cell which contains data validation (defined by .SpecialCells(xlCellTypeAllValidation)
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

'If an error occurs, go to the label "exitHandler"
On Error GoTo exitHandler

'If there are no cells which contain data validation, then goto the label "exitHandler"
If rngDV Is Nothing Then GoTo exitHandler

'If the "target" cell (the changed cell) is NOT one of the cells in rngDV (is a cell containing data validation)
If Intersect(Target, rngDV) Is Nothing Then
    'The cell changed was not a data validation cell.  You are doing nothing here.
       'do nothing
    Else
    'The cell changed was a data validation cell.  You are doing things here.
    
    'Turn off application events to prevent the Worksheet_Change macro from firing again, thus creating a potential infinite loop
    Application.EnableEvents = False
    
    'Define the variable "newVal" to be the new value of the changed cell
    newVal = Target.Value
    
    'Undo the changed cell
    Application.Undo
    
    'Define the variable "oldVal" to be the old value (achieved by undoing the change we just made) of the changed cell
    oldVal = Target.Value
    
    'Redefine the cell's new value by calling the newVal variable
    Target.Value = newVal
    
    'If the changed cell's column is 24 then...
    If Target.Column = 24 Then
        'If old value was blank then...
        If oldVal = "" Then
          'do nothing
        Else
            'If new value is blank then...
            If newVal = "" Then
              'do nothing
            Else
                'newVal was not blank.  Concatenate oldVal and newVal together
                Target.Value = oldVal & ", " & newVal
            End If
        End If
    End If
End If

'exitHandler Label - here the Application events are being turned back on
exitHandler:
  Application.EnableEvents = True
End Sub

 

[iliace]

Any time the value changes in a cell that contains a validation rule, the new value is appended to the previous value with a comma separator.

 

[shg]

If you make a change to a cell with data validation in col X, it replaces the new value with a catenation of the prior cell contents and the new contents.

It could be done more simply like this. In the sheet module,

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sOld          As String
  Dim sNew          As String

  With Target
    If .Cells.Count = 1 And HasValidation(.Cells) And _
       Not IsEmpty(.Value) And .Column = 24 Then
      sNew = .Value
      On Error GoTo Oops
      Application.EnableEvents = False
      Application.Undo
      sOld = .Value
      .Value = IIf(Len(sOld), sOld & ", ", "") & sNew
    End If
  End With

Oops:
  Application.EnableEvents = True
End Sub

In a standard code module:

Function HasValidation(r As Range) As Boolean
  On Error Resume Next
  HasValidation = Not Intersect(r.Cells(1), r.Worksheet.Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing
End Function

 

[SanFelippo]

Thanks! That makes sense for the sheet is referencing.