Using Data Validation With a Custom Formula

Topic

New Reply

Several years ago I picked-up a workbook here (or maybe from another forum) that was developed as a vehicle mileage log. It uses Data Validation | Custom | Formula to insure the beginning mileage is equal to or greater than the ending mileage for a particular vehicle’s prior entry in the log.

I have attached an abbreviated version of the workbook. The formula is =G14>=MAX(IF($C$3:$C13=$C14,$H$3:$H13)). It works great! However, each time the workbook is opened, the user must highlight cells G4:G16 (where the Data Validation’s custom formula is applied), then open the Data Validation dialog box and click the OK button. The formula is there, so you don’t have to do anything but click the OK button.

I don’t recall exactly why you go through this process of selecting the cells and opening the Data Validation dialog box, etc., but I know if you don’t, the validation process will not work. It appears Excel does not retain the Data Validation’s formula and I assume this process is reapplying the Data Validation’s formula to those cells. Or that’s the case in Excel 2010.

As long as I was the only user inputting data into the workbook, remembering to reapplying the formula was not a problem. However, recently my wife started entering data as well. She often forgets to go through the process to reapply the formula; this allows her to enter a beginning odometer that’s earlier than the last ending odometer reading; which she’s prone to do.

Here’s an example, if you open the attached workbook and enter a vehicle in C14 (select from drop-down list: Vehicle 1) and enter a Beginning Odometer mileage of 105,300 (G14), the data entry is accepted. In reality this should not be possible because the prior Ending Odometer for Vehicle 1 was 105,306 (G11). Now, if you highlight G4:G16, then then open the Data Validation dialog box and click the OK button and use the same example, when you attempt to enter 105,300 in G11 you receive appropriate warning message associated with the Data Validation’s formula.

Sorry to be so long winded, but I hope someone will have a suggestion or alternative method to accomplish this task (other than to ensure my wife enters the correct data or remembers the process to reapply the Data Validation’s formula…Ha!).

Thank you.

Reply | Quote

Replies

sbdale,

If you don’t mind using macros here’s one that will automatically place the previous high mileage in the Beginning Odo field:

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim lCurRow     As Long
  Dim lRowCntr    As Long
  Dim zVehicle    As String
  Dim lCurMileage As Long
  Dim bFoundLast  As Boolean

  If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
  If Target.Value > 0 Then Exit Sub
  
  zVehicle = Target.Offset(0, -4).Value
  
  If Trim(zVehicle) = "" Then
    MsgBox "You have not selected a Vehicle!" & vbCrLf & vbCrLf & _
           "Please select a vehicle before attempting to enter Mileage.", _
           vbOKOnly + vbCritical, "Error: Unknown Vehicle!"
    Exit Sub
  End If
  
  lCurRow = Target.Row
  bFoundLast = False
  
  For lRowCntr = lCurRow - 1 To 3 Step -1
     If Cells(lRowCntr, 3) = zVehicle Then
       lCurMileage = Cells(lRowCntr, 8)
       Target.Value = lCurMileage
       bFoundLast = False
      End If
  Next lRowCntr
  
  If Not bFoundLast Then
    Target.Value = _
        InputBox("Please enter the starting odometer reading for " & _
                 zVehicle, "Warning: Previous Mileage not found!")
  End If
    
End Sub

Messages provided:

If you don’t select a vehicle before attempting to enter a beginning mileage.
41527-novehicle

If the code can’t find a previous ending mileage for the selected vehicle.
41530-nomileage

Test File: 41529-Mileage-Log

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi
I thought there would have been a method to refresh the validation. I can’t find one.

I started playing around trying to test if the current cell had validation applied and if not then apply some.
In this example the range h1:h5 had a list of colours.

I would be interested to watch for others’ comments on this.

I wondered if an auto macro could be run on workbook_open that selected the last cell in the mileage column and checked for validation.
The little bit of following code is ugly but it works. I don’t like creating and then trapping for error conditions. Somebody might have a better idea.

In this example I am looking for a validation type. These types have constants 0-7.
I created the validation code in the With Selection.validation … End with structure by recording the macro that resulted when I clicked the validation button.

I added some more thoughts later in the day.

Code:

Sub TestValidation()
On Error GoTo errorHandler
If ActiveCell.Validation.Type > -1 Then
    'MsgBox (" has validation")
    Exit Sub
End If
errorHandler:
Select Case Err.Number
    Case 1004
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$H$1:$H$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Case Else
    'add stuff here
End Select
End Sub

Additional thoughts.
I have attached a workbook with an auto workbook_open macro in the this workbook object.

The code checks the first empty cell in the column for data validation and if necessary adds a validation rule. I also rejigged the error checking a bit for discussion.

41532-validationTest

Cheers
G

Reply | Quote

I made some tweaks to RG’s nice code to eliminate some bugs.

1. Multiple selection causing mismatch error
2. Leaving the warning message in input box then clicking OK places warning message in target cell and yields an error in total miles driven column.
3. Error message popup will not allow entry in column G if target cell is blank.
4. Place last ending mileage for vehicle as default starting mileage
5. Added a handle for selecting Cancel in the input box.
6. Added data validation to make sure starting mileage is not less than previous ending mileage and mileage is numeric.
7. Re-prompt added if invalid mileage entered
8. Added offset.select at end to allow for repeat cell selection.

HTH,
Maud

Changes highlighted in blue

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim lCurRow     As Long
  Dim lRowCntr    As Long
  Dim zVehicle    As String
  Dim lCurMileage As Long
  Dim lMileage
  [COLOR=”#0000FF”]If Target.Count > 1 Then Exit Sub[/COLOR]
  If Intersect(Target, Range(“G:G”)) Is Nothing Then Exit Sub
  
 [COLOR=”#0000FF”] If Trim(Target.Offset(0, -4)) = “” Then[/COLOR]
    MsgBox “You have not selected a Vehicle!” & vbCrLf & vbCrLf & _
           “Please select a vehicle before attempting to enter Mileage.”, _
           vbOKOnly + vbCritical, “Error: Unknown Vehicle!”
    Exit Sub
  End If
  
  lCurRow = Target.Row
  zVehicle = Target.Offset(0, -4).Value
  
  [COLOR=”#0000FF”]For lRowCntr = 3 To lCurRow – 1[/COLOR]
     If Cells(lRowCntr, 3) = zVehicle Then
       lCurMileage = Cells(lRowCntr, 8)
       Application.EnableEvents = False
       Target.Value = lCurMileage
       Application.EnableEvents = True
      End If
  Next lRowCntr
[COLOR=”#008000″]’—————————–
‘RE-PROMPT[/COLOR]
[COLOR=”#0000FF”]GetMileage:[/COLOR]
    lMileage = InputBox(“Please enter the starting odometer reading for ” & _
            zVehicle, , lCurMileage, vbOKOnly + vbInformation)[COLOR=”#008000″]’—————————–
‘ENTRY VALIDATION[/COLOR]
 [COLOR=”#0000FF”]   If lMileage = False Or lMileage = “” Then
        Exit Sub
    ElseIf lMileage < lCurMileage Then
        MsgBox "You cannot enter a milage less than the ending milage (" & lCurMileage & ").  Try again!"
        GoTo GetMileage
    ElseIf Val(lMileage) = 0 Then
        MsgBox "Please enter a numeric mileage value greater than or equal to ending mileage.  Try again!"
        GoTo GetMileage
    Else:
        Target = lMileage
    End If
    Target.Offset(0, 1).Select[/COLOR]End Sub

Reply | Quote