The Object code has suddenly stopped working in one of my spreadsheets

[Mackeral]

The following is in the appropriate Worksheet Object:

Private Sub Worksheet_Change(ByVal Target As Range)
    Stop
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Stop
End Sub

When I change a value in a cell or change the cell to be selected in that Sheet, I get the "STOP's" indicated in the code.

My problem is that in another spreadsheet, I am depend on the same Object code working, but it is "turned off": ie. the code I expect to work is never started..
Any idea why it is turned it off and what I can do to turn it back on.

Thank for any help, Mac

 

[jasonb75]

You can only turn such things on or off at application level, if it is working in one then it should work in the other.

The most likely thing is that it is not in the correct module or that there is a slight error in the name which is invalidating it.

Application.EnableEvents = True

in the immediate window will turn them back on if you have inadvertently turned them off.

 

[Michael M]

Also have a look at events being disabled at some point in other codes and not being returned to true.
To rectify this, simply put

 Application.enableEvents=True

in the Immediates window and press Enter

 

[Mackeral]

That was the first thing I looked at. It still doesn't work.

But thanks for reading this.
Mac

 

[Mackeral]

And this works on other spreadsheets.

 

[MARK858]

What other code have you got. In particular look in the ThisWorkbook module.

 

[jasonb75]

And this works on other spreadsheets.

If you're using the 'Stop' test to check if it is firing and nothing happens then that points to a naming or location error, otherwise I would say that there is potential for an incorrect logical test to be bypassing the body of the code.

If you've checked that EnableEvents = True then unless you're going through the convoluted process of opening multiple instances of excel (holding the Alt key and opening excel twice) you can not disable events in a way that they will start in one workbook but not another.

As you're using worksheet events they must be in the correct worksheet module and in the correct workbook, e.g. to run on Sheet1 of Book1 the code must be in the worksheet module of Sheet1 in the Book1 project, if it is in a regular module, a different worksheet module, a workbook module, or any module in another workbook then it will not run.

Another thing that I have seen a few times is where people rename things, for example adding a number to the end if the name in order to have multiple events with the same name.

Private Sub Worksheet_Change2(ByVal Target As Range)

Doing this invalidates the procedure name and prevents if from firing.