Results 1 to 4 of 4
2015-08-01, 14:04 #1
- Join Date
- May 2002
- 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!).
2015-08-01, 15:33 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,533 Times in 1,390 Posts
If you don't mind using macros here's one that will automatically place the previous high mileage in the Beginning Odo field:
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
If you don't select a vehicle before attempting to enter a beginning mileage.
If the code can't find a previous ending mileage for the selected vehicle.
Test File: Mileage Log.xlsm
2015-08-01, 18:17 #3
- Join Date
- Apr 2001
- Levin, Manawatu-Wanganui, New Zealand
- Thanked 28 Times in 26 Posts
Validation on Open
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.
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
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.
Last edited by geofrichardson; 2015-08-02 at 02:29. Reason: Added further material
2015-08-02, 11:40 #4
- Join Date
- Aug 2010
- Pa, USA
- Thanked 693 Times in 629 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.
Changes highlighted in blue
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
The Following User Says Thank You to Maudibe For This Useful Post: