Solved

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

Posted on 2002-07-01
5
1,219 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Per recommendation, points refunded and question closed.

Netminder
EE Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel: Combine Columns Vertically 2 36
Excel 2010 - Select category per Max values v.2 2 34
Excel Max 6 31
MS Word Formatting 3 25
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Outlook Free & Paid Tools
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now