Solved

Excel: Disable Macro Break

Posted on 2001-06-29
12
783 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:MichaelWray
  • 7
  • 5
12 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6237292
What operations are done in the unlocked state ? Avoiding the problem usualy is better than solving them.
0
 
LVL 2

Author Comment

by:MichaelWray
ID: 6237333
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
 
LVL 13

Expert Comment

by:cri
ID: 6237420
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:cri
ID: 6237465
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
 
LVL 2

Author Comment

by:MichaelWray
ID: 6237492
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
 
LVL 2

Author Comment

by:MichaelWray
ID: 6237499
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
 
LVL 13

Expert Comment

by:cri
ID: 6237512
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
 
LVL 13

Accepted Solution

by:
cri earned 300 total points
ID: 6237599
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
 
LVL 13

Expert Comment

by:cri
ID: 6237610
0
 
LVL 2

Author Comment

by:MichaelWray
ID: 6240235
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
 
LVL 2

Author Comment

by:MichaelWray
ID: 6240238
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
 
LVL 13

Expert Comment

by:cri
ID: 6242009
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question