+ Reply to Thread
Results 1 to 5 of 5

Sheet Change Event stops working

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Sheet Change Event stops working

    my worksheet change even keeps stopping for range (B10:B47). I added "Application.EnableEvents = True" into my code but it still stops. When it stops I run the below code with that in it and it works again. Do I have it placed into my first code correctly or what need to be changed

      Private Sub Worksheet_Change(ByVal Target As Range)
        
                        'On Error GoTo ErrorHandler
        
        Dim EndTime As Range
        Dim SBlank As String
        SBlank = """" & """"
        Set EndTime = Range("B10:B47")
        
        'ActiveSheet.Protect Password:="Financial1", UserInterFaceOnly:=True
        ActiveSheet.Protect , UserInterFaceOnly:=True
        
        Application.EnableEvents = True
        
        If Target.Column <> 2 Or Target.Count > 1 Or Target.Row < 10 Or Target.Row > 47 Then Exit Sub
        
        If Not Application.Intersect(EndTime, Range(Target.Address)) Is Nothing Then
            If Target.Value = "" Then
                Target.Offset(1, -1).Value = ""
                GoTo 99
            End If
            
            
            'If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then
                'Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.Row & ")," & SBlank & ",B" & Target.Row & ")"
            'End If
                    If Intersect(Target, Range("B10:B47")) Is Nothing Then Exit Sub
                    Dim xHour As String
                    Dim xMinute As String
                    Dim xWord As String
                    Application.EnableEvents = False
                    xWord = Format(Target.Value, "0000")
                    xHour = Left(xWord, 2)
                    xMinute = Right(xWord, 2)
                    On Error Resume Next
                    Target.Value = TimeValue(xHour & ":" & xMinute)
                    On Error Resume Next
                    Application.EnableEvents = True
       
              If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then
                Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.Row & ")," & SBlank & ",B" & Target.Row & ")"
            End If
                
            
        End If
        
                                Application.EnableEvents = True
                                
    'ErrorHandler:
                                
                                'Resume Next
                                'Application.EnableEvents = True
        
        
    99:
        End Sub
    when I run this it starts to work again

        
        Sub Restart()
    Application.EnableEvents = True
    End Sub

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Sheet Change Event stops working

    Put a break point at the beginning of the code.

    Trigger the change event and step through line by line and I'm sure you will see that after it's set to false the code is not running a line that sets it back to true.

  3. #3
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Sheet Change Event stops working

    I am not sure how to do what you are suggesting

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sheet Change Event stops working

    some light reading on vba debugging

    https://www.techonthenet.com/excel/m...kpoint2013.php
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Sheet Change Event stops working

    Setting a breakpoint.

    Set a break point on the first line of code, then when the code is triggered it will stop on the first line.

    You can then step through the code line by line by continuously pressing F8.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. worksheet_change event stops working
    By kutach770 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2023, 12:21 PM
  2. [SOLVED] formula stops working when I change a letter.
    By scudo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-31-2015, 04:44 PM
  3. Macro that runs for every sheet stops working
    By cooket4 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-08-2014, 09:58 AM
  4. [SOLVED] change event AND calculate event on the same sheet
    By lauriejerome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2013, 03:54 PM
  5. Excel paste special stops the sheet from working
    By hazzaska in forum Excel General
    Replies: 3
    Last Post: 01-08-2013, 09:11 PM
  6. Sheet protection stops macro working.
    By Andrew-Mark in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-18-2009, 11:18 AM
  7. Replies: 5
    Last Post: 06-23-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1