Excel - tactics for complex validation

Question

Hello All,

I seem to have a dilemma. I have an EXCEL 2003 template which users should use to fill in tabular information. I have validations on various cells and each row undergoes a rather complex VBA validation upon change and selection_change events. The sheet is protected to disallow formatting activities, insert and delete of rows and columns, etc.

As long as users fill in the table row by row, all works pretty fine. Things get worse if I want to allow user to copy/paste data into that sheet (which is a legitimate user demand in this case), because cell validation would disallow paste actions.

So I tried to allow users to turn off protection and cut/paste, a VBA marks the sheet to indicate the fact that it contains unvalidated entries. I have created a "batch validation" that validates all non-empty rows at once. Still copy/paste doesn't work too well (must directly jump from source sheet to destination, cannot paste from text files, etc.)

Cell Validation is also not good from the point of inserting rows, because depending on where you insert the row, cell validation may be missing completely. And if I copy cell validations down to row 65k the empty sheet gets over 2M in size - another most unwanted side effect.

So I thought one way to circumvent troubles would be to forget about cell validation alltogether and use only VBA. Then I would sacrifice user comfort of providing drop-down lists in some columns - some of which change as a function of entries in other columns, too.

Has anyone been in the same situation before and can give me some (generic) tactical advises (coding VBA is not a problem)?

Kind regards MikeD

Answer 1

I've had a similar project where I resorted to trapping the paste event and forcing a pastespecial of just values. That preserves the formatting and conditional formatting/data validation, but allows the user to paste values in. It does, however destroy the ability to undo the paste.

Answer 2

I believe it is possible to capture the "paste" event. I don't remember the syntax, but it will give you an "array of cells" to be copied, as well as the top-left cell where the cells are being copied.

If you modify a cell's value in vba you don't need to deactivate the validations at all - so what I would do is (sorry, pseudo-code, my VBA is a bit rusty)

OnPaste(cells, x, y)
  for each cell in cells do
    obtain the destinationCell (using the coordinates of cell on Cells, plus x and y)
    check if the value in cell is "valid" with destinationCell's validations
    if not valid, alert a message
    if valid, destinationCell.value = cell.value
  end
end

Answer 3

Here is what I came up with (all Excel 2003)

All sheets in my workbook requiring complex validation are organized in tabular form with a couple of header lines containing sheet title and column titles. All columns right of the last are hidden, and all rows below a practical limit (in my case 200 rows) are hidden as well. I have set up the following modules:

• GlobalDefs ... Enums
• CommonFunctions ... functions used by all sheets
• Sheet_X_Functions ... functions particular to a single sheet
• and event triggers in Sheet_X itself

The Enums purely serve the purpose of avoiding to hardcode; should I want to add or remove columns I mostly edit the Enums, while in the real code I use the symbolic names for each column. This may sound a bit over-sophisticated, but I learned to love it when users came for the 3rd time and asked me to modify the table layouts.

' module GlobalDefs
Public Enum T_Sheet_X
    NofHRows = 3    ' number of header rows
    NofCols = 36    ' number of columns
    MaxData = 203   ' last row validated
    GroupNo = 1     ' symbolic name of 1st column
    CtyCode = 2     ' ...
    Country = 3
    MRegion = 4
    PRegion = 5
    City = 6
    SiteType = 7
    ' etc
End Enum

First I describe code which is event triggered.

Suggestions in this thread was to trap PASTE activities. Not really supported by an event trigger in Excel-2003, but finally not a big miracle. Trapping/Untrapping the PASTE occurs on activate/deactivate events in Sheet_X. On Deactivate I also check protection status. If unprotected I ask the user to agree to a batch validation and re-protect. Single Line validation and Batch validation routines then are code objects in module Sheet_X_Functions described further down.

' object in Sheet_X
Private Sub Worksheet_Activate()
' suspend PASTE
    Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste" ' main menu
    Application.CommandBars("Edit").Controls("Paste Special...").OnAction = "TrappedPaste" ' main menu
    Application.CommandBars("Cell").Controls("Paste").OnAction = "TrappedPaste" ' context menu
    Application.CommandBars("Cell").Controls("Paste Special...").OnAction = "TrappedPaste" ' context menu
    Application.OnKey "^v", "TrappedPaste" ' key shortcut
End Sub

' object in Sheet_X
Private Sub Worksheet_Deactivate()
' checks protection state, performs batch validation if agreed by user, and restores normal PASTE behaviour
' writes a red reminder into cell A4 if sheet is left unvalidated/unprotected
Dim RetVal As Integer
    If Not Me.ProtectContents Then
        RetVal = MsgBox("Protection is currently turned off; sheet may contain inconsistent data" & vbCrLf & vbCrLf & _
                        "Press OK to validate sheet and protect" & vbCrLf & _
                        "Press CANCEL to continue at your own risk without protection and validation", vbExclamation + vbOKCancel, "Validation")
        If RetVal = vbOK Then
            ' silent batch validation
            Application.ScreenUpdating = False
            Sheet_X_BatchValidate Me
            Application.ScreenUpdating = True
            Me.Cells(1, 4) = ""
            Me.Cells(1, 4).Interior.ColorIndex = xlColorIndexNone
            SetProtectionMode Me, True
        Else
            Me.Cells(1, 4) = "unvalidated"
            Me.Cells(1, 4).Interior.ColorIndex = 3 ' red
        End If
    ElseIf Me.Cells(1, 4) = "unvalidated" Then
        ' silent batch validation  ... user manually turned back protection
        SetProtectionMode Me, False
        Application.ScreenUpdating = False
        Sheet_X_BatchValidate Me
        Application.ScreenUpdating = True
        Me.Cells(1, 4) = ""
        Me.Cells(1, 4).Interior.ColorIndex = xlColorIndexNone
        SetProtectionMode Me, True
    End If
    ' important !! restore normal PASTE behaviour
    Application.CommandBars("Edit").Controls("Paste").OnAction = ""
    Application.CommandBars("Edit").Controls("Paste Special...").OnAction = ""
    Application.CommandBars("Cell").Controls("Paste").OnAction = ""
    Application.CommandBars("Cell").Controls("Paste Special...").OnAction = ""
    Application.OnKey "^v"
End Sub

Module Sheet_X_Functions basically contains the validation Sub's specific to that sheet. Note the use of Enum's here - it really paid off for me - especially in the Sheet_X_ValidateRow routine - users forced me to change this a felt 100 times ;)

' module Sheet_X_Functions
Sub Sheet_X_BatchValidate(MySheet As Worksheet)
Dim VRow As Range
    For Each VRow In MySheet.Rows
        If VRow.Row > T_Sheet_X.NofHRows And VRow.Row <= T_Sheet_X.MaxData Then
            Sheet_X_ValidateRow VRow, False ' silent validation
        End If
    Next
End Sub

Sub Sheet_X_ValidateRow(MyLine As Range, Verbose As Boolean)
' Verbose: TRUE .... display message boxes; FALSE .... keep quiet (for batch validations)
Dim IsValid As Boolean, Idx As Long, ProfSum As Variant

    IsValid = True
    If ContainsData(MyLine, T_Sheet_X.NofCols) Then
        If MyLine.Cells(1, T_Sheet_X.Country) = "" Or _
           MyLine.Cells(1, T_Sheet_X.City) = "" Or _
           MyLine.Cells(1, T_Sheet_X.SiteType) = "" Then
            If Verbose Then MsgBox "Site information incomplete", vbCritical + vbOKOnly, "Row validation"
            IsValid = False
        ' ElseIf otherstuff
        End If

        ' color code the validation result in 1st column
        If IsValid Then
            MyLine.Cells(1, 1).Interior.ColorIndex = xlColorIndexNone
        Else
            MyLine.Cells(1, 1).Interior.ColorIndex = 3  'red
        End If

    Else
        ' empty lines will resolve to valid, remove all color marks
        MyLine.Cells(1, 1).EntireRow.Interior.ColorIndex = xlColorIndexNone
    End If

End Sub

supporting Sub's / Functions in module CommonFunctions that are called from the above code

' module CommonFunctions
Sub TrappedPaste()
    If ActiveSheet.ProtectContents Then
        ' as long as sheet is protected, we don't paste at all
        MsgBox "Sheet is protected, all Paste/PasteSpecial functions are disabled." & vbCrLf & _
               "At your own risk you may unprotect the sheet." & vbCrLf & _
               "When unprotected, all Paste operations will implicitely be done as PasteSpecial/Values", _
               vbOKOnly, "Paste"
    Else
        ' silently do a PasteSpecial/Values
        On Error Resume Next ' trap error due to empty buffer or other peculiar situations
        Selection.PasteSpecial xlPasteValues
        On Error GoTo 0
    End If
End Sub

' module CommonFunctions
Sub SetProtectionMode(MySheet As Worksheet, ProtectionMode As Boolean)
' care for consistent protection
    If ProtectionMode Then
        MySheet.Protect DrawingObjects:=True, Contents:=True, _
                        AllowSorting:=True, AllowFiltering:=True
    Else
        MySheet.Unprotect
    End If
End Sub

' module CommonFunctions
Function ContainsData(MyLine As Range, NOfCol As Integer) As Boolean
' returns TRUE if any field between 1 and NOfCol is not empty
Dim Idx As Integer

    ContainsData = False
    For Idx = 1 To NOfCol
        If MyLine.Cells(1, Idx) <> "" Then
            ContainsData = True
            Exit For
        End If
    Next Idx
End Function

One important thing is the Selection_Change. If the sheet is protected, we want to validate the line the user has just left. Therefore we have to keep track of the row number where we were coming from, as the TARGET parameter refers to the NEW selection.

If unprotected, the user could jump into the header rows and start messing around (allthough there are cell locks, but ....), so we just let not place him/her the cursor there.

' objects in Sheet_X
Dim Sheet_X_CurLine As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' trap initial move to sheet
    If Sheet_X_CurLine = 0 Then Sheet_X_CurLine = Target.Row

    ' don't let them select any header row    
    If Target.Row <= T_Sheet_X.NofHRows Then
        Me.Cells(T_Sheet_X.NofHRows + 1, Target.Column).Select
        Sheet_X_CurLine = T_Sheet_X.NofHRows + 1
        Exit Sub
    End If

    If Me.ProtectContents And Target.Row <> Sheet_X_CurLine Then
        ' if row is changing while protected
        ' validate old row
        Application.ScreenUpdating = False
        SetProtectionMode Me, False
        Sheet_X_ValidateRow Me.Rows(Sheet_X_CurLine), True ' verbose validation
        SetProtectionMode Me, True
        Application.ScreenUpdating = True
    End If

    ' in any case make the new row current
    Sheet_X_CurLine = Target.Row
End Sub

There is a Worksheet_Change code as well in Sheet_X, where I dynamically load values into the drop-down lists of fields of the current row based on the entry of other cells. As this is very specific, I just present the frame here, Important to temporarily suspend event processing to avoid recursive calls to the Change trigger

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsProtected As Boolean

    ' capture current status
    IsProtected = Me.ProtectContents

    If Target.Row > T_FR.NofHRows And IsProtected Then  ' don't trigger anything in header rows or when protection is turned off

        SetProtectionMode Me, False         ' because the trigger will change depending fields
        Application.EnableEvents = False    ' suspend event processing to prevent recursive calls

        Select Case Target.Column
            Case T_Sheet_X.CtyCode
                ' load cities applicable for country code entered
        ' Case T_Sheet_X. ... other stuff
        End Select

        Application.EnableEvents = True    ' continue event processing
        SetProtectionMode Me, True
    End If
End Sub

That's about it .... hope this post is usefull for some of you guys

Good luck MikeD

Answer 4

I personally think that messing fundamentally with cut'n'paste functionality in excel is a bad idea- and often has unintended consequences, like the breaking of undo for example. Since it's possible to add the data validation via code, so why not just re-add it to the sheet in question after a paste? That would then also solve your incidental problem of inserting rows etc.

I tend to write simple subs that toggle these things on and off (e.g. with a parameter called "enabled" so it can be called to toggle off and toggle on again.

In the worksheet change event, you can then iterate through each cell and force the data validation (say for non-empty cells to prevent a raft of misfires when you insert a new row) and clear each pasted cell that fails the validation. To make this process a bit friendlier for the user, we tend to add a comment to the cell with the failed value before clearing it, and change the background colour of the cell so the user knows what bits they need to fix (obviously with a corresponding "clear all the comments" routine to run after the next validation.