Macro Stopped Working after I closed and Opened the spreadsheet

[laurens102]

I had a macro that worked just fine before I closed the spreadsheet. It is saved as a xlsm spreadsheet and my macro has been enabled in the trust settings. What would be causing my macro to stop working.

 

[Joe4]

I had a macro that worked just fine before I closed the spreadsheet. It is saved as a xlsm spreadsheet and my macro has been enabled in the trust settings. What would be causing my macro to stop working.

It could be different things based on what kind of code it is. Can you post the VBA code that is not running?

 

[laurens102]

It could be different things based on what kind of code it is. Can you post the VBA code that is not running?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng1 As Range
    Dim Rng1 As Range
    Dim xOffsetColumn As Integer
    Dim WorkRng2 As Range
    Dim Rng2 As Range

    xOffsetColumn = 3

'***FIRST BLOCK***
    Set WorkRng1 = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    If Not WorkRng1 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng1 In WorkRng1
            If Not VBA.IsEmpty(Rng1.Value) Then
                Rng1.Offset(0, xOffsetColumn).Value = Now
                Rng1.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng1.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

'***SECOND BLOCK***
    Set WorkRng2 = Intersect(Application.ActiveSheet.Range("G:G"), Target)
    If Not WorkRng2 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng2 In WorkRng2
            If Not VBA.IsEmpty(Rng2.Value) Then
                Rng2.Offset(0, xOffsetColumn).Value = Now
                Rng2.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng2.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

End Sub

 

[Joe4]

OK, I think I have a good idea what may be happening.

Do you understand what this line does?

Application.EnableEvents = False

This line temporarily disable events (like "Worksheet_Change") from firing.
Why would we want to use this?
Well, Worksheet_Change code is code that automatically runs when we update a cell.
But what if the VBA code inside of our Worksheet_Change procedure updates cells?
Then the code will call itself! If you are not careful, in some cases you can get caught up in and infinite loop, and your code will never stop and your Excel will freeze up!

So what we often do it temporarily disable events while the code makes cell updates so as not to trigger itself to run again.
Then, we usually turn it back on after the changes with the line of code:

Application.EnableEvents = True

However, what sometimes happens is in your testing, you get an error in the middle of your code, after the "...=False" line has run but before the "...=True" line has run.
In that case, events have been disabled and the code will not automatically run until you re-enable it.
There are two ways to do that:
1. Close Excel and re-open it to reset those settings
2. Run the following procedure manually

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Either one will re-enable the code to run again.

 

[laurens102]

OK, I think I have a good idea what may be happening.

Do you understand what this line does?

Application.EnableEvents = False

This line temporarily disable events (like "Worksheet_Change") from firing.
Why would we want to use this?
Well, Worksheet_Change code is code that automatically runs when we update a cell.
But what if the VBA code inside of our Worksheet_Change procedure updates cells?
Then the code will call itself! If you are not careful, in some cases you can get caught up in and infinite loop, and your code will never stop and your Excel will freeze up!

So what we often do it temporarily disable events while the code makes cell updates so as not to trigger itself to run again.
Then, we usually turn it back on after the changes with the line of code:

Application.EnableEvents = True

However, what sometimes happens is in your testing, you get an error in the middle of your code, after the "...=False" line has run but before the "...=True" line has run.
In that case, events have been disabled and the code will not automatically run until you re-enable it.
There are two ways to do that:
1. Close Excel and re-open it to reset those settings
2. Run the following procedure manually

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Either one will re-enable the code to run again.

Okay. So reopening the application allowed it to run again. Will this be an ongoing issue? I am just responsible for making the spreadsheet and this needs to work continuously as I will not be using it after it drops

 

[laurens102]

Okay. So reopening the application allowed it to run again. Will this be an ongoing issue? I am just responsible for making the spreadsheet and this needs to work continuously as I will not be using it after it drops

I think what is happening is the spreadsheet is protected, and password protected to allow ranges of cells to be used. This I believe is what is causing the code to fail. Is there a way to allow the macro to run when the passworded cells are unlocked with their corresponding passwords?

 

[Joe4]

I tried to explain the whole thing in my previous post so that it all makes sense.

As long as your VBA code run all the way through, and doesn't error our in between either of the:

Application.EnableEvents = False

and

Application.EnableEvents = True

sections, it should not happen again.

 

[Joe4]

I think what is happening is the spreadsheet is protected, and password protected to allow ranges of cells to be used. This I believe is what is causing the code to fail. Is there a way to allow the macro to run when the passworded cells are unlocked with their corresponding passwords?

Ah, critical detail that was not mentioned before!

You need to unprotect the sheet first to make changes to those cells.
You can do that in the VBA code. And then you will want to re-protect it again at the end of the code after the changes have been made.
See: How to PROTECT and UNPROTECT a Sheet using VBA in Excel