Warning Value Error ###

[GomaPile]

Hello Everyone, can someone please help ME if you have time, and would really appeaciate your support too.

Short background story - The spreadsheet is our monthly roster split into 2 fortnightly pay periods. We have 37 rows of Casuals with Shift Times being entered by Supervisors in military hours for some eg 0700-1500, 1500-2300 or 1600-0000 inside individual single cells, and with columns E8:E44 & F8:F44 downwards adds up the total sum number of hours worked - this works fine no real issues here.

The columns from E8:E44 & F8:F44 has the formula adding up Casuals row of shift times in cell E8 etc etc... downwards.

However, what I'm finding is our Supervisors are not entering data correctly making human mistakes (in my opinion poor laziness) which is causes a problem with the adding up the number of hours worked in the columns E8:E44 & F8:F44 and returns hash symbols ### there's something wrong in that row. Couple of errors I found for examples: 700-15000 or 150-2300

The VBA code below is meant to assist our Supervisors from those human or legitimate input data errors, would like to see a pop-up Warning Value Error ### message to let our Staff know you have entered a Shift Time incorrectly.

Apparently the VBA is not bringing no the WARNING messages to indicate there's an error.

Private Sub check(ByVal Target As Range)

If Intersect(Target, Range("E8:F44")) Is Nothing Then Exit Sub
If Target.Value <> "###" Then Exit Sub
MsgBox "You have entered an incorrect Shift Time " & Target.Address

End Sub

Thanks
Gary

 

[BSALV]

Private Sub Worksheet_Change(ByVal Target As Range)
     With Target
          If .Count > 1 Then Exit Sub                           'more then 1 cell at a time changed
          If Len(.Value) = 0 Then Exit Sub                      'empty
          If Intersect(Target, Range("E8:F44")) Is Nothing Then Exit Sub     'nothing changed in this range
          Select Case .Value
               Case Is < 0: .ClearContents                      'neg value
               Case Is <= 1                                     'between 0-1 = normal timevalues
               Case Is > 1                                      'miliatry times to be converted to normal times
                    s = Right("0000" & .Value, 4)               'add leading zeros and keep the 4 righest characters
                    On Error Resume Next
                    t = TimeValue(Left(s, 2) & ":" & Right(s, 2) & ":00")     'make it a time
                    On Error GoTo 0
                    If Err.Number <> 0 Then .ClearContents: Exit Sub     'check on non valid time
                    If Replace(Format(t, "hh:mm"), ":", "") = s Then .Value = t Else .ClearContents     'if the check isn't okay, clear or change in normal time
               Case Else: .ClearContents: Exit Sub              'check on non valid time
          End Select
     End With
End Sub

 

[KPKP]

I don't really have a solution for you, but some thoughts...Value errors are typically returned when something has been entered into a cell that Excel doesn't know how to handle. E.g., Excel is expecting a number but somebody enters text. Accidentally hitting the Spacebar in a cell will cause a Value error. Testing for ### won't work because that's not what's actually in the cell. That's just the error message that Excel is displaying. If shift times are a set of consistent known values, you might be better served by having staff pick from a drop-down pick list rather than manually entering their time.

 

[GomaPile]

Hi KPKP, I see your point. So you're saying there's no way for a VBA to show a pop-up a warning message to help the our Supervisors. I guess its hard to explain without actually seeing the entire workbook, as it contains high sensitive information although the spreadsheet. As you'd suggested using a drop-down, apparently that wont work either as all shift times can be different from time to time - I work in a hospital if that makes any sense, if you know what I mean by that too. Just looking for something fancy hoping to flag the 3 hashes ### if they do appear anywhere or any value error with a pop-up message they have data entered incorrectly.


Hi BSALV, thank you so much for you help too. Really didn't want our Shift Times to be converted back to hh:mm hours. Again as above without actually seeing the entire workbook its can be difficult to explain what I'm trying to achieve.

Thanks Heaps Guys
Gary

 

[BSALV]

If you can't explain, then it can't be made.
The good one's (0 to 2359) are checked and those who pass, remain untouched.
If they didn't pass the check, you get a msgbox and the value is deleted.
All other entries are deleted without warning.

Private Sub Worksheet_Change(ByVal Target As Range)
     With Target
          If .Count > 1 Then Exit Sub                           'more then 1 cell at a time changed
          If Len(.Value) = 0 Then Exit Sub                      'empty
          If Intersect(Target, Range("E8:F44")) Is Nothing Then Exit Sub     'nothing changed in this range
          Select Case .Value
               Case 0 To 2359                                   'miliatry times to be converted to normal times
                    s = Right("0000" & .Value, 4)               'add leading zeros and keep the 4 righest characters
                    On Error Resume Next
                    t = TimeValue(Left(s, 2) & ":" & Right(s, 2) & ":00")     'make it a time
                    On Error GoTo 0
                    b = (Err.Number <> 0)
                    If Not b Then b = (Replace(Format(t, "hh:mm"), ":", "") <> s)
                    If b Then
                         MsgBox "Cell : " & .Address & vbLf & "Value : " & .Value, vbCritical, UCase("You have entered an incorrect Shift Time ")
                         .ClearContents                         'if the check isn't okay, clear or change in normal time
                    End If
               Case Else: .ClearContents                        'check on non valid time
          End Select
     End With
End Sub

 

[GomaPile]

Good morning BSALV thanks mate understood, we appreciate your help

Gary

 

[BSALV]

you're welcome and by the way, here it's evening?