Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel: Disable Macro Break

Posted on 2001-06-29
12
Medium Priority
?
801 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

772 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