Excel: Disable Macro Break

I have some spreadsheets in Excel 2000 that are password protected. A few of the macros unlock the sheet and when they have finished password protect the sheet again.

However, if the user hits the break key the macro does not complete and the sheet remains unprotected. I have generally tackled this by keeping sensitive information off of sheets that need to be unprotected and reprotected.

However, I though I could use an "onkey" or some such statement to get the macro to reprotect before stopping. But I can't seem to get it to work.

Can anyone tell me how best to achieve this end?

Thanks,
Michael.
LVL 2
MichaelWrayAsked:
Who is Participating?
 
criConnect With a Mentor Commented:
There are two alternatives, one quick and dangerous, one more elaborate:

Sub DisableCtrlBreak()
  Application.EnableCancelKey = xlDisabled
  MsgBox "Try 'Ctrl+Break' now"
  MsgBox "Use this sparingly or you will not be able to break out of a loop"
End Sub


Sub TrapCtrlBreak()
  On Error GoTo myErrorHandler
  MsgBox "Try 'Ctrl+Break' now"
 
myErrorHandler:
  If Err.Number = 18 Then
    MsgBox "Ctrl+Break was performed. Msgbox for testing, call protecting procedure here.."
    On Error GoTo 0 'reset error handler
  End If
End Sub
0
 
criCommented:
What operations are done in the unlocked state ? Avoiding the problem usualy is better than solving them.
0
 
MichaelWrayAuthor Commented:
Hi Cri.

For most of them it's inserting a row and taking the contents that the user enters into an input box into the row.

Cheers,
Michael.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
criCommented:
I hoped it involved Groups or AutoFilters which can be enabled for a protected worksheet.

You can catch the Ctrl+Break for Excel, still looking for the VBE object.

Nonetheless:

Sub RedefineCtrlBreak(Optional sSubName As Variant)
  Application.OnKey "^{BREAK}", sSubName
End Sub

Sub InformNoCtrlBreak()
  MsgBox "Ctrl+Break disabled for now, run RedefineCtrlBreak w/o parameter to reset it"
End Sub

Sub testit()
  RedefineCtrlBreak ("InformNoCtrlBreak")
  MsgBox "OnKey does only apply to Excel, so this Dialog can be 'Ctrl+Break'ed"
End Sub
0
 
criCommented:
At the moment all I can think of are work-arounds:

a) Keep the unprotected time period as short as possible, just for the inserting/writing and make sure you do display a inputbox during this time. If possible do this as batch, otherwise define auxiliary subs to protect/unprotect.

b) Consider using a suitable Event like 'Workbook_BeforeSave' to re-protect all sheets case your Subs where breaked unduly.
0
 
MichaelWrayAuthor Commented:
Cri,

This is similar to what I'd already tried and did not prevent the Excel macro from breaking.

I put the following 2 lines as the first commands in my macro:
Application.OnKey "^{BREAK}", ""
Application.OnKey "{BREAK}", ""

And ended my macro with:
Application.OnKey "^{BREAK}"
Application.OnKey "{BREAK}"

However, I'm just thinking that maybe there is another factor. I'm using a Compaq laptop. The break function is selected by simultaneously pressing Fn+PgDn (as break is the "lower function" of the PgDn key. Perhaps I should take your suggestion to a desktop PC at work tomorrow and test there. If this sounds like it might be the problem, do you know how to specify the Fn key - like + is shift. I tried F14 and +F14, as that's the corresponding of that key on the Compaq keyboard.

Anyhow, I'll try the normal break and ctrl+break on a desktop keyboard tomorrow and comeback then.
Cheers,
Michael.
0
 
MichaelWrayAuthor Commented:
Cri,

This is similar to what I'd already tried and did not prevent the Excel macro from breaking.

I put the following 2 lines as the first commands in my macro:
Application.OnKey "^{BREAK}", ""
Application.OnKey "{BREAK}", ""

And ended my macro with:
Application.OnKey "^{BREAK}"
Application.OnKey "{BREAK}"

However, I'm just thinking that maybe there is another factor. I'm using a Compaq laptop. The break function is selected by simultaneously pressing Fn+PgDn (as break is the "lower function" of the PgDn key. Perhaps I should take your suggestion to a desktop PC at work tomorrow and test there. If this sounds like it might be the problem, do you know how to specify the Fn key - like + is shift. I tried F14 and +F14, as that's the corresponding of that key on the Compaq keyboard.

Anyhow, I'll try the normal break and ctrl+break on a desktop keyboard tomorrow and comeback then.
Cheers,
Michael.
0
 
criCommented:
No need to test, as I stated above it will catch the Ctrl+Break in Excel but _not_ the VBA/VBE part (i.e. during a macro execution or dialog mode).

Is the operation this time consuming that the users start hitting Ctrl+Break ?


0
 
MichaelWrayAuthor Commented:
Both your alternatives are good. The first one, as you say, is the quick method. It will be good when I need a quick enable and I can guarantee there are no chances of looping or hanging that I will need to get out of. As an aside, I generally include a counter in my macros where looping is possible, with a goto end if the counter reaches a certain number.

The second method is better, as I can issue a msgbox telling the user what has happened - after locking back up of course. Whilst you didn't specifically state that I need to include the statement Application.EnableCancelKey = xlErrorHandler at the beginning of the process, I soon realised something was needed.

So thanks Cri - this is exactly what I needed! I will accept your answer now.

Cheers,
Michael.
0
 
MichaelWrayAuthor Commented:
I knew someone like Cri - who seems to answer LOTS of questions - would be able to help. And I'm glad to say he came through very very quickly.
0
 
criCommented:
MichaelWray, thAnk you. Regarding the '=xlErrorHandler', the second example worked w/o, but certainly it is better to observe the rules as shown in my last link.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.