Solved

Excel/VBA Message - "Can't enter break mode at this time"

Posted on 2002-07-01
5
1,309 Views
Last Modified: 2012-08-13
I have a workbook with (among others) worksheets named 'Week 1' to 'Week 13'
There is a menu from which a user can select to 'Move on a week'
In this process 'Week 1' is removed, Week 2' becomes Week 1 etc and a formatted but blank sheet becomes week 13.

The macro code is meant to :-
1. cut out all the controls on worksheet 'Week 1' (Combo boxes, buttons etc)
2. delete all VBA code from the worksheet ('Week 1')
3. move 'Week 1' to a new book, (which will then be saved and closed)

the process should happen in that order but in any order, the message 'Can't enter break mode at this time' is displayed. When I click continue, the process then runs on, but I want to remove this problem because the users will start laying duckeggs.
It doesn't matter by the way if I take out process 1 or processes 1 and 2 the message always comes up. I'd be grateful for any incites.

0
Comment
Question by:macbone2
  • 2
  • 2
5 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7122442
Hi macbone2,

some thought and some reading of several posts on this error let me to this (could be totally wrong here)

this is a post with some methods to relieve you from your problem

question:
When the following command line is executed, "Can't break mode at this time" message with Continue, End, and Help enabled appears. I don't know why this happens.

ThisWorkbook.Worksheets("Master Invoice").Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

The thing is that I made a reference to another workbook and switch between the referring and the referred workbooks quite often. I wonder if that might cause "Can't break mode at this time" message...

Worksheets("Master Invoice") contains three worksheet-level names and a picture. Since I want to keep those in newly created worksheets, I like to use the method of "Copy after:=", rather than "worksheets.add & 
.cells.copy " which doesn't copy the picture and incorrectly define names.
---


Response 1:
As I expected I had no problems running this code. If there is a possibility of confusion concerning which is 'ThisWorkbook' you might be better off creating a
variable for the workbook object,

Dim wbkCurrent As Workbook
Set wbkCurrent = Workbooks("Current.xls")
With wbkCurrent
.Worksheets("Master Invoice").Copy _
after:=.Worksheets(.Worksheets.Count)
End With
Set wbkCurrent = Nothing
---

Response 2:
I use xl97sr2 and never had a problem with a macro that copied another workbook.worksheet into a different workbook.

then one day, I started getting this error.

The suggestion was to turn off the "require variable declaration" in side Tools|options of the VBE.

I did that and it worked ok ever since. (Although I do have to type "option explicit" for each module.)

---------------

read all this and more, but what seems to be the problem is that you delete a sheet in the workbook and excel still needs some variables in it, on the deletion or the move to the other workbook it could be that excel is confused about the variables  

just a thought

HAGD:O)Bruintje
0
 
LVL 2

Author Comment

by:macbone2
ID: 7123374
Hi Brian,
Thanks for your response. The error comes up even before I try to Move the sheet. It happens when I try to 'Cut' the controls out of the sheet, or if I skip that code and try to delete the vba code. I think it's something to do with the Worksheet code attempting to run, even though I start the macro with Application.EnableEvents = False.
It's got me baffled.
 Geoff
0
 
LVL 2

Author Comment

by:macbone2
ID: 7126798
The error first appears at the line Selection.cut in the following code (Sheet  "Week 1" has previously been selected in the current workbook)
CODE
On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Cut    '** 'Can't enter break mode at this time'
If Err.Number <> 0 Then
MsgBox "Error number " & Err.Number & vbLf & _
"Desc. " & Err.Description, vbOKOnly
End If

The process then runs on to end on selcting Continue. Is there a way of intercepting the dialog box. I can't trap it with on error. If I can intercept, I can possibly branch round it so the user doesn't have to.

0
 
LVL 44

Expert Comment

by:bruintje
ID: 7456759
Hello macbone2

this question is open for more then 2 months
time to clean up
if not stated otherwise

my recommendation will be
-PAQ and refund
-this will be finalized by an EE Moderator
-with no further update (23.11.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
posted by ToolzEE v1.0
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7488936
Per recommendation, points refunded and question closed.

Netminder
EE Admin
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

808 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