Free Microsoft Excel 2013 Quick Reference

cannot execute code in break mode?

I'm a a complete novice with VBA. I have set up a sheet with macro buttons that hide/unhide rows to give a different display on screen. I have also set up a reset button which i have set to return all cells to a default status and hide all the rows except those of the first display. Everything seems to work ok except the reset button.
I get the message "cannot execute code in break mode". Can anyone help?


I occasionally set a break point and get a message "Cannot execute code in break mode". I feel confident that it's an "Application.OnTime" procedure that tries to run, and I understand that.

But, while in break mode, I can go to the Immediate Window and run subs from there. I can also go to Excel and run my Custom Menu Items. Neither of these things affect the break mode. After running my subs, VBE returns to the line that has the break on it and I can continue.

Why don't I get the "Cannot execute code in break mode" message when I run a sub from the Immediate Window, or a Menu Button in Excel?

I have been typing in code in the VBA editor, then flipping back to excel and running the code as a sub. About every third or fourth line I add to the code, when I try to execute it in excel, i get a "can't execute code in break mode" error message. Usually I have to fiddle with the highlighted line in the VBA editor to get the code to work. Most often I just add the "continue line" charecter somewhere in the middle of the line, e.g.:

Range("a1") = _
2 + 2

Though this works it results in ugly code. I know my identation and the amount of blank lines I use to sepearte code blocks are far from the norm. Could that be the cause?

I seem to get these errors quite a lot and they are very annoying.

Thanks for all help.

I generated a MDE file in Access 2000 with a report whose data source is a query. Because we have 15 people to use this MDE file, I use another separated MDB file to save data and then import linked table from this MDB file to MDE file. In the other words, everybody has MDE file in his/her own computer, but share the same MDB data in a network drive. For that report, it works very good in all other computers except one win98 machine. When I click the generate report icon, it always pop up an error message:

this action will reset the current code in break mode.
Do you want to stop the running code? To halt select yes.
To leave the code in current state select No.

then the query freezes.

I don't think my query has problem, because it works fine in any other computers. This computer uses Windoes 98, and access 2000 has only SR-1. then I upgrade it with SP-3, but the problem is still there. From some research, some people think it is a microsoft bug. Does anybody have this problem before? Any clue for the solution? If you need more description, please let me know. Thank you very much and have a great season.

When I run the following piece of code in break mode (using F8 to process 1 line at a time) it works fine:


	VB:
	
 
rAppID.Copy 
With wsTotals 
    With .Cells(1) 
        .PasteSpecial xlValues 
    End With 
End With 
 'Set rTotalsID = ActiveCell.CurrentRegion
 
 'Add extra Column, "A" becomes "B"
With wsTotals 
    .Columns(1).EntireColumn.Insert 
    .Rows(1).EntireRow.Insert 
    .Range("b1").Value = "App IDs" 
End With 
Set rBEnd = Range("B65536").End(xlUp) 
Set rBTemp = Range("b1", rBEnd) 
 
 'Filter out duplicates and copy unique list to "A"
 '    .Range("A1").CurrentRegion.Clear
rBTemp.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsTotals.Range("A1"), Unique:=True 
 
 'Add extra Column, "B" becomes "A"
wsTotals.Columns(2).EntireColumn.Delete 
 
 
Set rTotalsID = wsTotals.Range("A1", Range("A1000").End(xlUp)) 
Set rTotalsKwh = rTotalsID.Offset(0, 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The dataset (strings) are copied to the proper sheet, a new column is inserted, the unique values are transferred to column A and the source column is deleted. When I attempt to run this through though, I get the error method range of object worksheet has failed. Looking at the sheet, the filtered data has been deleted and so the sheet is blank. I assume the script fails because there is no dataset on which to search for that last cell.

I have read through the forums here and elsewhere and have seen the suggestion to clear the current range. This is a newly created sheet though, on which no operations have been performed before this section of code. Nonetheless, I have attempted that, to no avail. I also inserted the code to add a header to that data column - also with no luck.

Any advice would be appreciated.

riddley

I am new to this post, and am not sure the correct method to post, so if I do not do it right I apologize up front.

I am working on an Excel document that will be used for numerous people to enter project information. So, I wanted to create a question section which will unhide hidden rows, so that the spreadsheet only displays relevant information.

The macros I have used for the checkboxes are like this:

Sub CheckBox188_Click()
With ActiveSheet
If .CheckBoxes(Application.Caller).Value = xlOn Then
.Range("47:49").EntireRow.Hidden = False
Else
.Range("47:49").EntireRow.Hidden = True
End If
End With
End Sub

They work just fine until I protect the sheet and the workbook. Then I get an error stating that Microsoft Visual Basic "cannot execute code in break mode." I am very new to macros and have no idea how to correct this. I really want to have everything protected so that I don't have to contanstantly fix formulas and such.

Any help would be very appreciated. Thank you in advance.

Also, a followup question: Is there a way to hide other check boxes when you hide the row that contains the boxes?

Thank you.

Hi,

It's very weird. My code gets stuck here when I run it in run time. However, when I run it in Break mode it works FINE.
I have a lot of code which works perfectly fine before this. Any solutions to this? I tried using the timer before this code stars to stop running the code for a few seconds, but it didn't help.
Error: Columns F, G & H get deleted.


	VB:
	
 FCall() 
    Worksheets("PreFinal").UsedRange.Copy 
    Worksheets("Final").Select 
    Worksheets("Final").Pastespecial 
    Columns("F:G").Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance.

I have about 30-40 people using a file with a user form I wrote, it works fine for all but one person.. that person keeps randoming having the macro start in break mode. There is not any errors in the code so it's not like it's just breaking upon an error. The individuals are all using the same computers and running the same version of excel ...

She doesn't appear to have any additional add-in's compared to others... the problem seems to occur more often when she hasn't rebooted lately but not always.

I have had others download the current file version and it works great for them... I have also had her download an updated version of the file several times.. but it doesn't solve the issue ... so it's not like she accidently broke the code at some point in time and it messed up the running of the file.

Ideas?

Currently, I have an Input Box that allows the user to either input annual salary ex: 50000 or hourly rate ex: 25.42. I am trying to create a code that will look at the value of the input box and if there is an hourly rate, then multiply it by 2080, otherwise keep the annual salary value. In excel, it would work something like this:

=if(iserror(search(".",value of the input box)),value of input box,value of input box * 2080)

When I tried to create something like that in VBA, I either got the error msg: Can't Execute code in break mode OR Compile Error: Sub or Function not defined.

Here is what I tried to do:


	VB:
	
Value4 = InputBox(prompt:="Please enter annual salary or hourly rate.") 
ActiveCell.FormulaR1C1 = Value4 
ActiveCell.FormulaR1C1 = _ 
"=IF(ISERROR(SEARCH(""."",r[1]c)),r[1]c,r[1]c*2080)" 
ActiveCell.Next.Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can someone help me with this? I've tried different variations, but would only get a circular reference or some other cell that is not the active cell. I also tried to create an If Then Else statement, but didn't get any results there either. Here's the other variation I did:


	VB:
	
Value4 = InputBox(prompt:="Please enter annual salary or hourly rate.") 
ActiveCell.FormulaR1C1 = Value4 
 
If IsError Search(""."", Value4) Then 
     
    ActiveCell = Value4 
     
Else 
     
    ActiveCell = Value4 * 2080 
     
    EndIf 
    ActiveCell.Next.Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I got this error msg: Compile Error: Expected list separator or )

Any help you can give me with this would be greatly appreciated.

I'm currently studying loops and having a minor issue. The example formula in the book for this particular exercise is:

Code:
Sub FillRange()
     Dim Count as Integer
     For Count = 1 To 100
          ActiveCell.Offset(Count -1,0) = Rnd
     Next Count
End Sub
And it works perfectly, except, as expected, it gives me random numbers between 0 and 1. I'm trying to rewrite it to give me consecutive whole numbers from 1 to 100. Is there a simple way to write that?

Second, I have a recurring problem when running subs I've written. I often get an error message that says "Can't Execute Code in Break Mode". I sort of half-understand what that means, but since I'm not doing it manually: a. What causes it, and more importantly b. How can I exit Break Mode?

this is what i came up with, but i'm getting an error. (Can't execute code in break mode)

ActiveCell.FormulaR1C1 = "1"
Range("A2").AutoFill Destination:=Range("A2:LastRow"), Type:=xlFillSeries
Range ("A2:LastRow")

I have a spreadsheet with column A empty. i've a vba code that sorts a range (A-AO, down to LastRow). I'm trying to populate cell A2 (A1 is header) with the number 1. then autofill down column A, until it gets to the LastRow...which is different each time i'll use this macro.

I'm posting separately as it's a different topic and will help with search function.

thank you.

So this code works for Excel 2003:
Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

However when I was trying to get it to work for someone, they were using an
older version of Excel (I believe it was 97), so how can I get the code to
work to get the spell check to work under protection? I kept getting error
messages such as:

"Can't execute code in break mode"

and

"Compile error: named argument not found" and then it highlights this part
of the code: SpellLang:=1033

Ok,

I'm trying to schedule a mail being sent out every day, with an attachment. However I don't want it to run on a weekend (ie weekday is 1 or 7). The onTime method does work to the extent that it counts down and runs the routine, however it just keeps running it thereafter and I end up sending the same mail over and over. I have no idea why. Well, I think its to do with the schedule property of the onTime method, its set to true by default. But when I try and set it to False, I get a 1004 error. At my wits end here, can anyone help? Here's the code:

Sub evalDay()

sTimer = Now + TimeValue("23:59:59")
mSent = False
If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
ElseIf WeekDay(Now) <> 7 And WeekDay(Now) <> 1 Then
Application.OnTime sTimer, "sendMenu.xls!Sheet1.sendIt"
End If
End Sub
-------------------------------------------------------------------------

Sub saturday()
Application.OnTime TimeValue("23:59:58"), "sendMenu.xls!sheet1.sunday"
Loop
End Sub
-------------------------------------------------------------------------
Sub sunday()
Do While Time <> "23:59:59"

If Time = "23:59:59" Then Application.OnTime Now + TimeValue("8:59:59"), "sendmenu.xls!sheet1.sendIt"
Loop
End Sub
-------------------------------------------------------------------------
Sub sendIt()

If WeekDay(Now) = 7 Then
saturday
ElseIf WeekDay(Now) = 1 Then
sunday
Else
Set oApp = CreateObject("outlook.application")
Set oItem = oApp.createItem(olMailItem)

oItem.attachments.Add ("FP06common$FacilitiesEurest - RestaurantMenusMenu.doc")
oItem.To = "Clevedon Support Centre"
oItem.Body = "Hi," & Chr(13) & Chr(13) & "Please find attached this weeks menu. You can access the ordering system from within the attached menu." & Chr(13) & Chr(13) & "Regards" & Chr(13) & "Joe Foster"
oItem.Send

Set oApp = Nothing

End If
End Sub
-------------------------------------------------------------------------

I can't even step through the code to check for the error because the timers get all screwy and I get messages about not being able to execute code in break mode. Irritating to say the least!!!!!

Help much appreciated.
Joe

So this code works for Excel 2003:
Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

However when I was trying to get it to work for someone, they were using an
older version of Excel (I believe it was 97), so how can I get the code to
work to get the spell check to work under protection? I kept getting error
messages such as:

"Can't execute code in break mode"

and

"Compile error: named argument not found" and then it highlights this part
of the code: SpellLang:=1033

I'm new on VBA programming, I've actually tried it the first time a few days ago.
Now, I have a combo box with 4 drop down lines. Now what I want is that when let's say I choose the first line, the active cell gets the background color green; the second blue, the third red and the 4th yellow.

Well, I thought I made a pretty solid formula, but as it shows, it doesn't work

This is how it looks like.
When I try to run it the error "Variable not defined" shows up and the word "ComboBox" in line 2 is highlighted.

Sub cmd_ComboBox_Click()
Selection.Value = ComboBox.Value
If Value = "Weiterbildung" Then
Selection.Interior.Color = 12611584
End If
If Value = "Urlaub" Then
Selection.Interior.Color = 65535
End If
If Value = "Krank" Then
Selection.Interior.Color = 255
End If
If Value = "Anwesend" Then
Selection.Interior.Color = 5296274
End If
End Sub

I've already added this formula to a combobox in my sheet and always when I click on an item in the dropdown list, I get the message: "Can't execute code in break mode"

Does somebody know how to handle this? Or maybe what mistake I made in my formula?

Thanks alot.

I am receiving the error "Cant execute code in break mode" on the line:
 in the code below
This is to assign the macro HideCrews to each of the form buttons on the worksheets
Sub AssignMacro()
Dim i As Integer

With ActiveSheet
For i = 1 To .Shapes.Count
If .Shapes.Item(i).Type = msoFormControl Then
.Select
    If Selection = xlButtonControl Then
    .Shapes.Item(i).OnAction = "HideCrews"
    End If
End If
Next i
End With

End Sub
i want this macro to run when i click the button
Sub HideCrews()

    Dim i As Integer
    Dim j As String
    Dim Col As String
    Dim Sht As String

    
    Application.ScreenUpdating = False
    
    Col = Sheets("Data").Cells(8, 1).Value
    
    Sht = ActiveSheet.Name
    
    Sheets("Data").Visible = True
    Sheets("Data").Select
    Sheets("Data").Range(Col & "3").Select
    Selection.End(xlDown).Select
    j = ActiveCell.Row
    
    For i = 3 To j
    On Error Resume Next
    Sheets(Sht).Select
    If (Sheets("Data").Cells(i, Col).Offset(, 1).Value = "0") Then
    Sheets(Sht).Range(Sheets("Data").Range(Col & i)).EntireRow.Hidden = True
    End If
    Next i

    Application.ScreenUpdating = True
    
End Sub
How do i fix this error?

So this code works for Excel 2003:
Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

However when I was trying to get it to work for someone, they were using an
older version of Excel (I believe it was 97), so how can I get the code to
work to get the spell check to work under protection? I kept getting error
messages such as:

"Can't execute code in break mode"

and

"Compile error: named argument not found" and then it highlights this part
of the code: SpellLang:=1033

Hi
I have recorded a macro to sort within a range and have assigned it to a
forms-button. It runs well while executing on XP machine. But people who try
to run it from their 2000 OS get the following error.
"Can't execute code in break mode"
I am not able to reproduce this error on my XP machinee. Please help

When trying to debug some of my code in step mode for an Excel 2000
spreadsheet I get to this particular
line and it just stops.

ActiveWorkbook.Worksheets("StatusData").Select
On Error Resume Next
>>> strTest = Range("Vision").Text <<<< stops on this line
If (Err.Number <> 0) Or (strTest = "FALSE") Then

The error object is not filled, nothing. It acts as is the statement was
"End".

strTest is Dim'd as a string.
The range "Vision" that I am looking for does not exist (that's why I'm
doing in line error checking).

The code works fine when the subroutine is run normally.

Any ideas?

thanks

I have an application-level event procedure in an .xlam add-in, ThisWorkbook!App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean). This is implemented with a module-level variable declaration, Private WithEvents App As Application, and this Workbook_Open event procedure:


	VB:
	
 Workbook_Open() 
    Set App = Application 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
App_WorkbookBeforeClose calls a sub on Module1 that in turn calls another sub, which, in part, adds a sheet if a condition is met.


	VB:
	
ActiveWorkbook.Sheets.Add after:=ActiveWorkbook.Sheets(Sheets.Count) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works as intended whenever a workbook is manually closed. However, when triggered by a workbook being closed programatically, it does not work. Stepping through the code in break mode it appears that the code is executed, as the above line of code is run and no errors are thrown, but when it completes, the sheet has not been added.

Just to put the functionality in context: The business requirement is that this code run against every workbook in a specific folder when they close. One of the workbooks is being opened and closed by a macro having nothing to do with my add-in, and when that macro closes the workbook, my App_WorkbookBeforeClose event fires and calls my code, which, as noted above, fails without errors.

Any help, insight, direction will be appreciated. Of course, let me know if you need any additional information or clarification. Thanks in advance for your time.

Hi all,
I am getting an error on a line of code that I cannot seem to work out. I am getting an "Invalid procedure call or argument" error when I try to add a title to a chart that I have created with vba. Here is what the creation of the chart looks like:


	VB:
	
 
 'Create the graph
ActiveSheet.Shapes.AddChart.Select 
ActiveChart.SetSourceData Source:=dataSheet.Range(sourceDataStart, sourceDataEnd) 
ActiveChart.ChartType = xlLine 
ActiveChart.SeriesCollection(1).Delete 
ActiveChart.SeriesCollection(1).Delete 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).Name = "=""Total Sightings""" 
ActiveChart.SeriesCollection(1).Values = dataSheet.Range(line1DataStart, line1DataEnd) 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(2).Name = "=""Closed Sightings""" 
ActiveChart.SeriesCollection(2).Values = dataSheet.Range(line2DataStart, line2DataEnd) 
ActiveChart.SeriesCollection(2).XValues = dataSheet.Range(xValuesStart, xValuesEnd) 
ActiveChart.Parent.Width = 500 
ActiveChart.Parent.Height = 200 
 
 'set the chart, x, and y axis titles
Dim xAxis As Axis 
Dim yAxis As Axis 
Set xAxis = ActiveChart.Axes(xlCategory) 
xAxis.HasTitle = True 
xAxis.AxisTitle.Caption = "Work Week" 
Set yAxis = ActiveChart.Axes(xlValue) 
yAxis.HasTitle = True 
yAxis.AxisTitle.Caption = "Sightings" 
yAxis.MajorUnit = 1 
ActiveChart.HasTitle = True 
 
 'GETTING MY ERROR ON THIS LINE
ActiveChart.ChartTitle.Text = "AN Presightings Per Work Week" 
 
 'create a pricture copy of the graph and delete the original
ActiveChart.CopyPicture 
dataSheet.Paste Destination:=dataSheet.Range("Q17") 
ActiveChart.Parent.Delete 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have marked the line that I have narrowed the error down to. Weird note, whenever I step through the code in break mode movingg one instruction at a time I do not get the error. I also don't get the error when I enter a breakpoint before the line (at some point after the chart is created) then I run the rest of the code at full speed. I don't think it is imoprtant, but I am getting the data for the chart on a separate worksheet named "dataSheet" but it is not the active sheet in my workbook. Any suggestions?

When trying to debug some of my code in step mode I get to this particular
line and it just stops.

ActiveWorkbook.Worksheets("StatusData").Select
On Error Resume Next
>>> strTest = Range("Vision").Text <<<< stops on this line
If (Err.Number <> 0) Or (strTest = "FALSE") Then

The error object is not filled, nothing. It acts as is the statement was
"End".

strTest is Dim'd as a string.
The range "Vision" that I am looking for does not exist (that's why I'm
doing in line error checking).

Any ideas?

thanks

Hello

I have a problem. This started because when running a certain VBA function my Excel Application would crash with "Microsoft Office Excel has encountered a problem and needs to close" error. The problem was that every time I ran the code in break mode it never crashed. So I read somewhere that this might be helped by running Help+Detect and Repair. I tried that and it crashed while executing. So I tried running the Microsoft Office Application Recovery. It succeeded but now every time I open Excel (Or any other MS Office Application) it runs several installs every time I use a feature which is extremely annoying. How do I solve this?

Also, does anyone know of any reason why my application would crash as described? It still does that.

Thanks in advance

Hello, i was hoping to get some help with some code i'm writing to apply conditional formatting via macro button. I keep getting a Run-time Error '5' (Invalid procedure call or argument)

Here's the code:


	VB:
	
 
With Range("G13:G2000").Select 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=OR(AND($G13""Completed"",$I13"",AND($G13""Closed"",$I13"")))" 
    Selection.FormatConditions(1).Interior.ColorIndex = 3 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The 3rd and 4th lines are highlighted when i view the code in break mode.

Thanks for taking a look.
(My apologies if code tags weren't used correctly - this was my first attempt to do so!)

Hey everyone,

I have a set of code that runs on the double-click event on a worksheet, to
display a user form to assist the user with entering data into the worksheet.
The user form does some data validation and then writes the values entered in
the form into the worksheet via VBA code.

The VBA code Unprotects the sheet, and re-protects before and after entering
the data. The ActiveSheet.Protect command is the very last line of code in
the subroutine before End Sub.

One user (and only one) of the sheet is getting the standard Microsoft Excel
error "The cell or chart you are trying to change is protected and therefore
read-only." after the code runs. This is despite the fact that the code has
already written all values to the sheet, and has re-protected as it's last
action before End Sub. The user is not triggering any other events which
would account for this error, and I don't know why Excel is giving the error,
nor can i work out what it is that is trying to change the data in the
worksheet.

I have tried it on their computer, stepping through my code in break mode,
and the error only comes up after the VBA has run and reached End Sub. There
are no events triggered by the user other than the initial double-click which
starts the code, and no other macros/code/events which are triggered in any
way by the user or my code.

Has anyone experienced this, and knows how to get around it?

Thanks,
Wayne