Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1744
  • Last Modified:

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

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
macbone2
Asked:
macbone2
  • 2
  • 2
1 Solution
 
bruintjeCommented:
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
 
macbone2Author Commented:
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
 
macbone2Author Commented:
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
 
bruintjeCommented:
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
 
NetminderCommented:
Per recommendation, points refunded and question closed.

Netminder
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now