1
\$\begingroup\$

I'm struggling to find the best way to prevent users from cancelling a routine halfway through (by pressing ESC, break, etc) without making sure the sheet is being locked again. On top of that, I also want to manage potential other errors that this routine could generate. My code is :

Private Sub Worksheet_Activate()

Application.OnKey "{ESC}", ""
Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = False
ActiveWindow.DisplayHeadings = False
On Error GoTo errHandler

On Error Resume Next
ActiveSheet.Unprotect "XXXXX"
ActiveSheet.ShowAllData
ActiveSheet.Range("estimate_table").AutoFilter Field:=59, Criteria1:=">0"
ActiveSheet.Protect "XXXXX"

Application.ScreenUpdating = True
Application.OnKey "{ESC}"
Application.EnableCancelKey = xlInterupt

exitHandler:
    ActiveSheet.Protect "XXXXX"
    Application.OnKey "{ESC}"
    Application.EnableCancelKey = xlInterupt
    Application.ScreenUpdating = True
    Exit Sub
errHandler:
    If Err.Number = 18 Then
    Resume exitHandler
    Else
    MsgBox "An error has occured. The list can't be updated."
    Resume exitHandler
    End If

End Sub

What I'm trying to achieve is run an autofilter on a table in a locked sheet every time the sheet is activated. I want users to be able to stop the routine if necessary (maybe it's taking forever) but I want to be sure that no matter what, the sheet is back to being locked.

Also, I noticed that sometime, if user stops the routine once and goes back and forth between sheets, the routine won't work anymore and will generate an error that is never reset or cleared (i.e. the message "An error has occured..blablabla" is displayed every time the user activates the sheet). So I want to be sure that the routine doesn't get blocked / bugged even if an error has occurred before (hence my On Error Resume Next).

Overall I'm not sure I'm doing it correctly here (not familiar with VBA and error handling in general). With the code above, It doesn't seem like I'm able to stop the routine at all (but least the sheet seems locked all the time, which is nice)

\$\endgroup\$
1
\$\begingroup\$

I think I've managed to improve (or clean at least) the code mentioned above:

Private Sub Worksheet_Activate()

Application.EnableCancelKey = xlErrorHandler
On Error GoTo errHandler
Application.ScreenUpdating = False
ActiveWindow.DisplayHeadings = False

ActiveSheet.Unprotect "XXXXX"
ActiveSheet.ShowAllData
ActiveSheet.Range("estimate_table").AutoFilter Field:=59, Criteria1:=">0"
ActiveSheet.Protect "XXXXX"

Application.ScreenUpdating = True
Application.EnableCancelKey = xlInterupt

Exit Sub

exitHandler:
    ActiveSheet.Protect "XXXXX"
    Application.EnableCancelKey = xlInterupt
    Application.ScreenUpdating = True
    Exit Sub
errHandler:
    If Err.Number = 18 Then
    Resume exitHandler
    Else
    Resume Next
    End If

End Sub

What I still don't understand is why it seems like I still can't stop the routine by pressing ESC (90% of the time, the process goes through and once in a while the process is indeed stopped)..

\$\endgroup\$
1
  • \$\begingroup\$ Getting votes for answering your own question on code review is very rare. It might be better if you removed this answer and modified the code in the question rather than add this answer. It's possible you have a timing issue. Code only answers are considered bad answers and may be deleted. Please add the VBA tag to your question to identify the language you are programming in. \$\endgroup\$ – pacmaninbw Apr 18 '20 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.