Auto Place : in Time Data Entry while Protected

[Bill Z]

Using Excel 2010 and have a work sheet protected and sent out for others to fill out and send back. We don’t want them to change any of the formats for obvious reasons.
One of the fields requires them to enter hours and minutes (in military format) but they don’t want to key the : and want to just enter 1330 and have Excel put in the :. The resulting time is used in calculating hours worked.

I wrote the macro below but and attached it to the sheet but it won’t run when the sheet is protected.

What should I do / try to get this seemingly simple request working?

Private Sub Worksheet_Change(ByVal Target As Range)
' cancels out if the conditions are not met
If Intersect(Target, Range("B17:B42")) Is Nothing _
Or Target.Count > 1 _
Or Not IsNumeric(Target.Value) _
Or Len(Target.Value) < 3 _
Or IsDate(Target.Value) Then Exit Sub
' force number format to text
Target.NumberFormat = "@"
' parse the value entered to put a colon right before the last 2 digits
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & ":" & Right(Target.Value, 2)
' force Text format to Time
Target.NumberFormat = "h:mm"
End Sub

I didn't know how to search for an already answered question.

[tarquinious]

Have you tried putting an ActiveSheet.Unprotect and ActiveSheet.Protect at the beginning and end of your function?

[Bill Z]

I tried to unprotect at the sheet level and at the cell level in the macro.

I think maybe this is the wrong direction. Something within Excel's date routines is messing me up.

Could there be another way of doing this?

[Bill Z]

I tried the ActiveSheet.Unprotect and ActiveSheet.Protect and when the macro executes it asks for the password to unprotect. The guys don't want to type in the : much less the password if we were to give it to them.