Excel has a good data validation tool to restrict the type, size or value of data which users enter into a cell which is easily accessible from the ribbon toolbar. There are times, however, I have found this feature to be messy or unworkable. To provide cell level data validation on worksheets with dynamic layouts I use Visual Basic for Applications (VBA.) This solution does not require much code and can be used to check data types (string, numeric, date, etc.), data character lengths and data values within specified ranges. Much of the validation (with error messages) can be done with 10 to 20 lines of code.

To get the validation to work you need to first trap a selection change (a movement from one cell to another), and then apply your validation code, if the cell’s value has changed. My code also reverts the cell’s value to the previous state so as to not cause any calculation errors.

There are 4 steps to this process:

• Set the lngOldVal variable at startup in the Workbook_Open() event procedure
• Reset the variable if the selection moves in the Worksheet_SelectionChange event  procedure
  
• Trap the Worksheet_Change event and run the data validation code. Replace the erroneous input with lngOldVal.
• Reset the variable if the selection moves in the Worksheet_SelectionChange event procedure

1 – Setting the initial old value variable

I use a Public variable to hold the value of the ActiveCell and declare it in my Main Module:

Option Explicit
Public lngOldVal As Long

The data type of the variable can be changed as required. It could also be a Variant type which covers most everything (including dates and strings) with the exception of “fixed length strings”. Since you shouldn’t need to state the length of the string when declaring this variable, a Variant would be a good “catch all” variable for this process. My code deals with numbers only, so a Long type is adequate in this case.

I also declare the bolChanged Boolean type variable to guard against inadvertently changing the variable lngOldVal. This declaration is done in the code for the worksheet to be validated. After the validation code is called and the errant input is replaced, the Worksheet_SelectionChange event is triggered again and this keeps the variable from changing to an incorrect value.

' Sheet1 code - (Main)
' this code was written by Richard Barnes
' ...and last modified on 10/1/2017

' Force explicit variable declaration
Option Explicit

Dim lastRow As Integer
Dim bolChanged As Boolean

The following code is placed in the Workbook_Open() procedure and initializes the variable to the ActiveCell value. The code sets the lngOldVal to the value of the ActiveCell when the workbook opens if the selection is within the range parameter used in the Intersect method. This ensures that the variable has a value since there has been no selection change.

' set the previous cell value for a startup value
    If Not Intersect(ActiveCell, Range("$D$17:$J$" & LastRow1)) Is Nothing Then
        lngOldVal = ActiveCell.value
    End If

The variable lastRow is the reason that Excel’s Data Validation is not a good solution with this workbook. The variable is a changeable value based on another custom VBA function (explained here.) which returns the last used row number. In this worksheet the number of rows is variable based on the user’s data insertions and deletions. lastRow equals “(FindLastRow(Sheet1) – 4)”, the last row which contains data minus the 4 constant end lines of the worksheet.

2 – Resetting the variable when the cell actually changes

The code which “resets” the lngOldVal variable when the the cursor moves from one cell to another is placed in the worksheet’s Selection_Change event procedure. When the selection changes, the code within the procedure is evaluated. The snippet of code shown will only run when the ActiveCell is within the Range(“$D$17:$J$” & lastRow). It is totally ignored if the cell is outside of the range.

The If statements evaluates 2 cases. The first is when the variable bolChanged is true. In this case the error message has been cleared and the ActiveCell’s value has been changed back to the previous (correct) value. All that is done in this case is the changing of the variable to false. This catches any additional Selection_Change events and doesn’t allow the changing of the variable lngOldVal. The second case is triggered when the selection changes, but before any editing to the cell’s value. This places a correct value in the variable lngOldVal which can be used to replace the erroneous input.

' set the Old value for this cell only if we are not processing a cell change
    If Not Intersect(ActiveCell, Range("$D$17:$J$" & lastRow)) Is Nothing Then
        If bolChanged = True Then
            bolChanged = False
        Else
            lngOldVal = Target.value
        End If
    End If

3 – Trap the Worksheet_Change event

The Selection_Change and the Worksheet_Change events are independent and do not trigger each other.  All the previously listed code simply sets and ensures a valid value for the lngOldVal variable. The data validation is triggered with a cell value change.

The following code in the Worksheet_Change event procedure is run when a cell changes value within the Range(“$D$17:$E$” & lastRow). First it checks for no value, then if the value is numeric or less than 0 (negative.) If the is NO value the cell reverts to the old value (to avoid calculation issues.) If it is not a number or is a negative number an error message is displayed and the cell reverts to the old value upon the closing of the message box.

' Materials, quantity and unit cost
    If Not Intersect(Target, Range("$D$17:$E$" & lastRow)) Is Nothing Then
        If Target.value = "" Or Target.value = vbNullString Then
            bolChanged = True
            Target.value = lngOldVal
            Target.Select
        ElseIf Not IsNumeric(Target.value) Or Target.value < 0 Then
            MsgBox "Only Positive Numbers are allowed for the Material costing values!", vbInformation, "Numeric values required..."
            bolChanged = True
            Target.value = lngOldVal
            Target.Select
        End If
        GoTo cleanup
    End If

Another benefit of using your own VBA validation is the ability to display an error message of any type or length and with different button configurations. A custom message box can also be used to trigger more code, such as canceling the cell value replacement or opening a form. Of course, you will be restricted to the available VBA message box options.

The GoTo Cleanup is code which moves the process to the end of the Worksheet_Change procedure and resets a few application level parameters such as ScreenUpdating = True and EnableEvents = True (among a few others.)

4 – Reset the variable if the selection moves in the Worksheet_SelectionChange

This is the start of editing a new cell.  If Worksheet_Change code found an error and corrected it, bolChanged is true, meaning any secondary or other triggered SelectionChange event does not alter lngOldVal. It remains the value of the current cell and bolChanged is switched to false. However a simple movement from one cell to another (within the listed Range) does change it since bolChanged is false. The If statement is necessary to avoid lngOldVal from changing to a wrong value during repeated incorrect inputs.

' set the Old value for this cell only if we are not processing a cell change
    If Not Intersect(ActiveCell, Range("$D$17:$J$" & LastRow1)) Is Nothing Then
        If bolChanged = True Then
            bolChanged = False
        Else
            lngOldVal = Target.value
        End If
    End If

That is it. Running the code, modified for your needs, of course, should provide your required data validation and an understandable error message explaining the situation.