Excel - tactics for comprehensive validation

Question

I seem to have a dilemma. I have an EXCEL 2003 template that users should use to populate tabular information. I have checks on different cells, and each row undergoes a rather complicated VBA check on change and selection_change events. The sheet is protected to prohibit formatting operations, insert and delete rows and columns, etc.

As long as users fill in the table row by row, everything works very well. Everything gets worse if I want to allow the user to copy / paste data to this sheet (which in this case is legitimate user demand), because checking the cell prohibits pasting actions.

So I tried to allow users to turn off protection and cut / paste, VBA marks the sheet to indicate that it contains unapproved records. I created a "batch check" that checks all non-empty strings at once. However, copying / pasting does not work too well (requires a direct transition from the source file to the destination, cannot be pasted from text files, etc.)

Cell check is also not very good from the point of row insertion, because depending on where you insert the row, cell check may be completely absent. And if I copy the cell check to line 65k, an empty sheet becomes more than 2 M in size - another undesirable side effect.

So, I thought that one way to get around trouble is to forget about checking alltogether cells and use only VBA. Then I would sacrifice the convenience of the user to provide drop-down lists in some columns, some of which also change depending on the entries in other columns.

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

Regards MikeD

Answer 1

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

All sheets in my book that require complex verification are organized as tables with several heading lines containing heading headings and column headings. All columns to the right of them are hidden, and all rows below the practical limit (in my case, 200 rows) are also hidden. I created the following modules:

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

Reserves exclusively serve to avoid hard code; if I want to add or remove columns, I mainly edit Enums, while in real code I use symbolic names for each column. This may seem a bit complicated, but I learned to love it when users came in for the third time, and asked me to change the layouts of the tables.

' 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 the code that is triggered by the event.

Suggestions in this topic were to capture the actions of PASTE. Not supported by event trigger in Excel 2003, but finally not a big miracle. PASTE trap / unpack occurs when events are activated / deactivated in Sheet_X. On Deactivate I also check the protection status. If I am not protected, I ask the user to agree to check and re-protect the package. Single line validation and batch validation procedures are then code objects in the Sheet_X_Functions module, described below.

 ' 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 

The Sheet_X_Functions module basically contains the confirmation suffix specific to this sheet. Pay attention to using Enum here - it really paid off for me - especially in the routine of Sheet_X_ValidateRow - users forced me to change this 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 

support for Sub / Functions in the CommonFunctions module 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 Selection_Change. If the sheet is protected, we want to check the line that the user has just left. Therefore, we must keep track of the line number where we came from, since the TARGET parameter refers to the NEW selection.

If it is not protected, the user can go to the title bar and start a mess (although there are cell locks, but ....), so we just do not place his / her cursor.

 ' 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 Worksheet_Change code in Sheet_X, where I dynamically load values ​​into drop-down lists of fields in the current row based on input from other cells. Since this is very specific, I just present the frame here. It is 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 

This is about him .... hope this post is useful to some of you guys

Good luck MikeD

Answer 2

I believe that you can capture the "paste" event. I don’t remember the syntax, but it will give you an “array of cells” for copying, as well as the top left cell in which the cells are copied.

If you change the value of a cell in vba, you don’t have to deactivate the checks at all - so I would do (sorry, pseudo-code, my VBA is a little 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 validations if not valid, alert a message if valid, destinationCell.value = cell.value end end 

Answer 3

I had a similar project in which I resorted to capturing a pasty event and highlighting the pasty properties of only the values. This retains the formatting and conditional formatting / validation of the data, but allows the user to insert values. He, however, destroys the possibility of canceling the paste.

Answer 4

I personally think that mess in principle with excel's cut'n'paste function is a bad idea - and often has unintended consequences, such as breaking cancellation. Since you can add data validation using code, why not just re-add it to the sheet in question after insertion? Then it will also solve your random problem of inserting rows, etc.

I try to write simple subtitles that turn these things on and off (for example, with the on option, so you can call it to switch and turn it on again.

In the worksheet change event, you can iterate over each cell and force a data check (for example, for non-empty cells to prevent skipping skips when inserting a new row) and clear every inserted cell that did not pass the test, To make this process a little friendlier for user, we usually add a comment to the cell with a failed value before clearing it and change the background color of the cell so that the user knows which bits they need to fix (obviously, using the procedure “clear all comments” to start after the next check.