Worksheet_Change problem

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The code you posted works fine for me. I can't reproduce your problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,805
Messages
5,598,173
Members
414,217
Latest member
Mystix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top