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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JoeMo

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,684
Members
431,828
Latest member
kARTIK12345

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