Stop other workbooks from using Error Handler

Other workbooks are not using your error handler; your error handler is disabling events, which affects all workbooks. Why are you disabling, rather than enabling, events in an error handler?

Here is more of the code. I will attach the file if necessary. I believe that other workbooks are using this error handler because maybe it's not specific enough, such as on error code: xxxx goto errhandler??

Whenever I open up a new workbook, I get a debugging prompt. It's because I'm trying to protect a specific worksheet name, and when I'm opening a new workbook, that sheet name does not exist.

I'd like to get more detailed in the error handler and if I cannot get past this situation, then I cannot get even more detailed in my error handler. (I have about 28 sheets, and I need to do things with these as well.)

Here is the code:

Private Sub Worksheet_Activate()
Run "AddMenus"

Dim r As Range, cell As Range

On Error GoTo ErrHandler

Set r = Me.Range("J13,J15:J18,J20:J21,J24,J26,J28:J30,J32,J43:J50,J53:J59,J70:J72,J74:J75,J81:J84,J86,J92:J99,J101:J107,V115:V150")

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cell In r
 If cell.Value = 0 Then
    Sheets("Balancing Sheet").Unprotect Password:="1234"
    cell.EntireRow.Hidden = True

 Else
    Sheets("Balancing Sheet").Unprotect Password:="1234"
    cell.EntireRow.Hidden = False

        
 End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Sheets("Balancing Sheet").Protect Password:="1234"

Sheets("Balancing Sheet").Protect Password:="1234"

End Sub

Private Sub Worksheet_Deactivate()
Run "DeleteMenu"
End Sub


Private Sub Worksheet_Calculate()

Dim r As Range, cell As Range

On Error GoTo ErrHandler

Set r = Me.Range("V35,J61:J62")

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cell In r
 If cell.Value = 0 Then
    Sheets("Balancing Sheet").Unprotect Password:="1234"
    cell.EntireRow.Hidden = True

 Else
    Sheets("Balancing Sheet").Unprotect Password:="1234"
    cell.EntireRow.Hidden = False

        
 End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Sheets("Balancing Sheet").Protect Password:="1234"

Sheets("Balancing Sheet").Protect Password:="1234"

End Sub

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

Try to discriminate between the scope of an error handler and the effect of the instructions in the error handle.

The scope or 'on error' is very local: it has no effect other than in the sub/function in which the 'on error' line resides. Your interpretation of what happens is therefore completely wrong.

if you put this instruction in an error handler:
application.visible=false
it will affect your whole Excel-session (and more). So this instruction has implications on application-level.

In most cases you don't need an error handler. It's better you write flawless code than to use an errorhandler.
In your case I don't see any necessity for an errorhandler.

And you forgot to put 'exit sub' just above the error handler. That means that the error handler will always be executed. That's not the idea of an error handler.

In your code 'Application.Enableevents' is redundant, because none of your instructions can trigger any event.

At last it isn't clear whether the sheet that's being activated in this event 'worksheet_activate' is the sheet "Balancing Sheet".
If not, the whole unprotect/protect code is useless, because the rows.visible operation takes place in the active sheet.

This code (or less) will suffice:

Private Sub Worksheet_Activate()
  Run "AddMenus"
  Application.ScreenUpdating = False

  Sheets("Balancing Sheet").Unprotect Password:="1234"
    for each cell In Range("J13,J15:J18,J20:J21,J24,J26,J28:J30,J32,J43:J50,J53:J59,J70:J72,J74:J75,J81:J84,J86,J92:J99,J101:J107,V115:V150")
    cell.EntireRow.Hidden = cell.value=0
   Next

   Sheets("Balancing Sheet").Protect Password:="1234"
   Application.ScreenUpdating = True
End Sub

Originally Posted by snb

In most cases you don't need an error handler. It's better you write flawless code than to use an errorhandler.

Just to add a bit of balance, I don't think I know a single programmer who would agree with that statement, especially not for people who are learning programming.

Once again, other workbooks cannot use that error handler. It would be helpful to see the workbook.