Solved

Excel: Disable Macro Break

Posted on 2001-06-29
12
784 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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