I am trying to figure out how to make use of the escape key be treated as an error.
My error handler looks like:In case the user hits the escape key, I want to kick the user out of the macro and protect the sheet.Errhandler: ThisWorkbook.Save MsgBox "Database Error. The database has been saved. Contact the Database Administrator." Call protectDatabase Resume Finish1:
Right now the macro hides sheets when it is done to keep the user out of certain areas. And soon the macro will have protection added to it so that when the macro is completed everything is locked down again.
In short, I want to make sure using the Esc key while the macro is running prevents the user from having access to the parts that are protected when the macro is not running.
Last edited by AKL01; 11-17-2011 at 08:35 AM.
Hi
Have a look at this
rylo
@rylo,
Thank you, appreciate the help. I hadn't found that post since I try to stick to the solved posts, but that did help.
For posterity, this also helped with error handling
Also, this is what my main code looks like with the error handler and the esc key function disabled.
Sub report() Application.EnableCancelKey = xlErrorHandler 'set Esc key to trigger the error handler On Error GoTo Errhandler: 'Activate the error handler Sheets("Processing").Activate 'give user something to look at while working Call unhideSHEETS 'unhides all workbook sheets Call setupREPORT 'do the setup (one time only) Do While Worksheets("DEBUG").Cells(7, 2).Value = 1 'while there are still RM items left Sheets("Processing").Activate 'give user something to look at while working Call screenSTOP 'stops screen updating Call theDECIDER 'Decide if/where where the current item goes into the report copyVAR = Worksheets("DEBUG").Cells(8, 2).Value 'retrive result of theDECIDER If copyVAR = 1 Then Call copyUNIT1 'copy the item to unit 1 Call sortDATA 'copy the item to SORT DATA ElseIf copyVAR = 2 Then Call copyUNIT2 'copy the item to unit 2 Call sortDATA 'copy the item to SORT DATA ElseIf copyVAR = 0 Then Call copyUNIT1 'copy the item to unit 1 and 2 Call copyUNIT2 Call sortDATA 'copy the item to SORT DATA End If Worksheets("DEBUG").Cells(4, 2).Value = Worksheets("DEBUG").Cells(4, 2).Value + 1 'increment i, the DB counter Call theCHECKER 'check to see if the end of the DB is reached Loop Call postPROCESS 'generate the KPI report Call sortSYS 'sorts CR in report for plotting Call filtUNQCOPY 'finds the unique data for plotting Call concatcount 'formats data for plotting Call MakeAPlot 'Plot the data for analysis Call hideSHEETS 'hides all sheets no longer needed Call screenSTART 'starts screen updating again Call protectDatabase 'lock down the database Sheets("REPORT").Activate 'end code on the report page GoTo Finish1: Errhandler: 'Errhandler used for any error or use of ESC key during processing Sheets("REPORT").Activate Range("A1:K039") = "" 'Clear report page to prevent use of erroneous data [a1] = "ERROR" ThisWorkbook.Save 'Autosaves database to capture error and any changed data MsgBox "Database Error. The database has been saved. Contact the Database Administrator." Call hideSHEETS 'Hide sheets no longer needed Call protectDatabase 'lock down the database Sheets("REPORT").Activate 'end code on the report page Resume Finish1: Finish1: Application.EnableCancelKey = xlInterrupt 'Set Esc back to interrupt End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks