Hi there
I have a problem with my code and I don't know why what's happening is happening. Basically when a user enters a time in HHMM format, the Worksheet_Change takes that user input and changes it into HH:MM format.
A message box comes up to confirm the change before changing the Target.Value. However, while waiting for a response from the MsgBox, the cell where the user has just entered data comes up with 00:00.
You can see in my code that I've tried to store the user entry into TimeStore variable (which the code then uses to change it to HH:MM format later) and then change the Target.Value to "" until later in the code when it changes the value to the time that was entered in HH:MM format.
Can someone have a look at the code and tell me how I can stop this from happening?
I have a problem with my code and I don't know why what's happening is happening. Basically when a user enters a time in HHMM format, the Worksheet_Change takes that user input and changes it into HH:MM format.
A message box comes up to confirm the change before changing the Target.Value. However, while waiting for a response from the MsgBox, the cell where the user has just entered data comes up with 00:00.
You can see in my code that I've tried to store the user entry into TimeStore variable (which the code then uses to change it to HH:MM format later) and then change the Target.Value to "" until later in the code when it changes the value to the time that was entered in HH:MM format.
Can someone have a look at the code and tell me how I can stop this from happening?
Code:
If Not Intersect(Target, Range("StartTime_Page")) Is Nothing Then
If IsEmpty(Target) Then
Prompt = MsgBox("Delete the start time?", vbYesNo, "Start Time")
If Prompt = vbYes Then
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
Else
Application.EnableEvents = False
TimeStore = Target.Value
Target.Value = ""
Application.EnableEvents = True
Select Case Len(TimeStore)
Case 1
NewTime = "00" & ":" & "0" & Right(TimeStore, 1)
Case 2
If Right(TimeStore, 2) > 59 Then
MsgBox "You have entered an invalid time.", vbCritical, "Invalid Time"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
NewTime = "00" & ":" & Right(TimeStore, 2)
Case 3
If Right(TimeStore, 2) > 59 Then
MsgBox "You have entered an invalid time.", vbCritical, "Invalid Time"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
NewTime = "0" & Left(TimeStore, 1) & ":" & Right(TimeStore, 2)
Case 4
If Left(TimeStore, 2) > 23 Then
MsgBox "You have entered an invalid time.", vbCritical, "Invalid Time"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
If Right(TimeStore, 2) > 59 Then
MsgBox "You have entered an invalid time.", vbCritical, "Invalid Time"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
NewTime = Left(TimeStore, 2) & ":" & Right(TimeStore, 2)
Case Else
MsgBox "You have entered an invalid time. Please enter the time in HHMM format.", vbCritical, "Invalid Time"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End Select
Prompt = MsgBox("Change the start time time to " & Format(NewTime, "hh:mm") & " ?", vbYesNo, "Start Time")
If Prompt = vbYes Then
Application.EnableEvents = False
Target.Value = TimeValue(NewTime)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End If
