Selected Answer
Hi RSWM
In the attached file, I've added this event macro (under the Workbook object in VB Project Explorer):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Range("E9").Value = "HOURS" Then
r = MsgBox(ActiveSheet.Name & " cell E9 read 'HOURS', correct things before saving?", vbYesNo, "E9 should read KM")
If r = vbYes Then Cancel = True
End If
End Sub
The bit in bold sees if E9 is "HOURS" and, if so, gives the user a popup and the chance to fix things.
I also added another similar event which triggers when a user moves to another worksheet- it tells them E9 was HOURS and if the chose Yes, goes back to that sheet. Here it is:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Range("E9").Value = "HOURS" Then
r = MsgBox(Sh.Name & " cell E9 read 'HOURS', go back to correct things?", vbYesNo, "E9 should read KM")
If r = vbYes Then Sh.Activate
End If
End Sub
You could delete the latter if you'd rather not have such reminders.
Hope this fixes things for you. If so, please remember to mark this Answer as Selected.