0

I have an excel sheet with many routines, sometimes they fail and I research the issue and fix it. BUt when a routine fails the code never finishes and sometimes it leaves my sheet unprotected. Is there a way to protect a sheet in the event of a VBA error?

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
MEC
  • 233
  • 3
  • 12

1 Answers1

0

See the code's comments and adjust it to include your code

Public Sub TestErrAndProtect()

' This line tells what to do and where to go when an error is raised
On Error GoTo CleanFail

    ' Your code goes here


CleanExit:
    ' Adjust ActiveSheet to the codename of the sheet if needed (see https://stackoverflow.com/a/52721327/1521579)
    ActiveSheet.Protect
    ' This line tells the program to exit the procedure
    Exit Sub

CleanFail:
    ' This line shows the error description in a message box
    MsgBox Err.Description
    ' This line tells the program to go to a label
    Resume CleanExit
End Sub

Let me know if it works and remember to mark the answer if it does.

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • Can I put this somewhere will it is global and will execute on any VBA error or do I have to put it in each sub routine? – MEC Jan 10 '20 at 02:44
  • You have to put it in each `Sub` – Ricardo Diaz Jan 10 '20 at 02:55
  • The starting part of your code says Public, do I place this under each Private sub – MEC Jan 10 '20 at 03:29
  • Nope. This was just an example. Your subs may be Private (doesn't matter). Begin with `On Error...` before your code. And after your code and before `End Sub` place `CleanExit...` – Ricardo Diaz Jan 10 '20 at 03:33