Solved

Excel: Disable Macro Break

Posted on 2001-06-29
12
785 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Outlook Free & Paid Tools
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

630 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