Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Using Data Validation With a Custom Formula

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    novehicle.JPG

    If the code can't find a previous ending mileage for the selected vehicle.
    nomileage.JPG

    Test File: Mileage Log.xlsm

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Validation on Open

    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.

    validationTest.xlsm

    Cheers
    G
    Last edited by geofrichardson; 2015-08-02 at 03:29. Reason: Added further material

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
      If Target.Count > 1 Then Exit Sub
      If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
      
      If Trim(Target.Offset(0, -4)) = "" 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
      zVehicle = Target.Offset(0, -4).Value
      
      For lRowCntr = 3 To lCurRow - 1
         If Cells(lRowCntr, 3) = zVehicle Then
           lCurMileage = Cells(lRowCntr, 8)
           Application.EnableEvents = False
           Target.Value = lCurMileage
           Application.EnableEvents = True
          End If
      Next lRowCntr
    '-----------------------------
    'RE-PROMPT
    GetMileage:
        lMileage = InputBox("Please enter the starting odometer reading for " & _
                zVehicle, , lCurMileage, vbOKOnly + vbInformation)'-----------------------------
    'ENTRY VALIDATION
        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).SelectEnd Sub
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-08-02)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •