Hi, I keep getting the error:
Compile Error: Ambiguous name detected: Worksheet_Change
I think I have to merge them together but can not figure it out.
Below is the code I am trying to get working.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' if cell that was changed was not in range named rngTimeEntry then
' exit this subroutine
On Error GoTo EnterTimeError
'A Static variable retains its value even after the procedure is run
'This is to prevent repetitive calls to the change event
Static toContinue As String
If toContinue = "No" Then 'already called for this target
toContinue = True
toContinue = "Yes"
If Intersect(Target, Range("B29:B47,Q29:Q52")) Is Nothing Then
' If cell that was changed has value outside range of 0001 - 2400, then
' prompt user to enter military time
If Target.Value = "" Then
If Target.Value < 1 Or Target.Value > 2400 Then
MsgBox "Enter Times in military format without a colon. Example: enter 1:00 PM as 1300.", vbExclamation
toContinue = "No"
Target.Value = ""
' Change military time format to HH:MM
toContinue = "No" 'next line again changes target which will again call change event - to prevent that set to No
Target.Value = Format(Target.Value, "00:00")
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C51:G58")) Is Nothing Then Else Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True