Breaking VBA code (2000)

Topic

New Reply

For some heretofore untold reason, I have JUST NOW figured out that going into the VBE Run menu, selecting “Break” is the same as setting Application.EnableEvents=False. (My wife has been making comments about all of the apostrophes I have been entering to stop the 200 or so lines of worksheet change and selection change events from occuring…please do not tell…she thinks I type 70 words per minute!).

Anyhow, in regards to adding the VBA code break syntax to a right click menu for my personal use, what would be the proper syntax/properties to use to temporarily “break” the code from executing? Is Application.EnableEvents=False the same as some “VBAProject.Code.Break=True???” syntax lurking somewhere that I have not found yet? I am somewhat leery of the EnableEvents method as I would suppose that it might not play well with any add-in code I have.

Reply | Quote

Replies

If I understand your question right, you want to programmatically do a ‘break’ in your code which will probably be used to debug some variables or allow you to single step through the code. Right?

Use ‘Stop’ on a line by itself.

When the code reaches a Stop statement, it stops and that line is highlighted as if you did a breakpoint.

I often use the Application.EnableEvents = False/True but you do need to be careful to re-enable them else your code is dead. I often use them when I need to stop triggering other events (infinite loop) such as update a cell via code which triggers the _change event. I have found, though that disable events doesn’t always work, there seem to be some actions which can’t be disabled.

Deb

Reply | Quote

Thanks Deb,

What I would like to do is this:

For example, take a worksheet that on activation unprotects the sheet, shows/hides certain columns, then reprotects the sheet. It also has selection change events and change events which do various similar things. Say that I manually unhide a column or row, and type in a new formula. All is well. However, if I right click on the cell and try to copy the formula down to other cells, the “Paste” option is grayed out, due to either the selection change or change event.

If I go into the VBE and choose “Break”, then back to the sheet, I can copy and paste due to the fact that the events are temporarily suspended.

I want to put a right click menu/button on the “cell” toolbar so I can do this by right clicking as opposed to opening the VBE, “Break”, and going back to the sheet. Maybe enable it if enableevents = true, disable it if enableevents=false.

I think I will try the enablevents method of breaking the code and see if that does what I want.

Reply | Quote

I now see what you want to do, it really has nothing to do wth Stop or enabling events. Since the sheet is protected, you get the non-functional Paste function on the cell right-click menu, that’s the problem. (In my tests, it’s not grayed out, just generates an error when you try and use it). You need a method whereby the code recognizes that it should temporarily unprotect that sheet and let you paste. The other way is to do your own paste function in code.

Some (not-so-brilliant-early-morning) ideas are:
1. If you only want to do this paste action on certain cells then add code in the _selection event that recognizes you just clicked on those cells. The code would then unprotect the code and let you copy/paste. After you’re done you’ll need to somehow trigger the code again to reprotect the sheet which can be done several ways. This isn’t a great solution since it relies on the user to re-initiate the sheet protection code again.

2. Add code to copy a formula from some source cell through to some destination. That’s the equivalent of you dragging the formula from one cell to another. If you want the sheet protected, you again need code to do this for you behind the scenes.

Your example said that the sheet is protected, some cols are hidden, etc. Then you say you manually unhide col/row and type in formulas. How can you do this on a protected sheet? Is it that only certain cells are unlocked and so can be edited while protected?

I played with some simplistic code to unprotect on right-click. It’s in the attached workbook. It’s written against Sheet1. When you right-click a cell, it prompts you (you can add code to request a password from user) and then unprotects the cells. This now lets you paste. After the paste, it’s protected again. Not a great solution but might give you ideas on where to proceed.

Deb

Reply | Quote

Thanks Deb,

When I say that certain columns and rows are hidden, these are hidden by the activate or one of the other events. I don’t mind unhiding them and unprotecting them as long as the code doesn’t lock me down before I can paste or do something else, i.e., the enableevents scenario. I really think the enableevents scenario will do the trick, just gotta put it in there. This way the code will be effective globally and not just with one sheet or book. Will try it and see.

Reply | Quote

The EnableEvents won’t solve your problem, it’s got nothing to do with Paste menu option. The sheet is protected so you need code to unprotect it allowing your to paste. That’s what my sample did.I do a lot of this sort of code, unprotect a sheet behind the scence to do work then reprotect it.

Good Luck,
Deb

Reply | Quote

Thanks Deb, added the enable/disable events to my right click add in just ’cause I have so much real estate there. Right you are, it is not the same as break mode in the VBE. Closest thing I could find was the VBE.ActiveVBProject.Mode property, which to my hilarious enjoyment is READ ONLY.

What I was driving at is that due to some of the worksheet events, the paste menu was grayed out. “Breaking” the code in the project allowed me to bypass the events and go ahead and paste. The worksheet was unprotected already, but has several hundred lines of code between the change and selection change events (the protecting, and hiding, etc, being just a part of it).

Reply | Quote

EnableEvents does (e.g.) not work with Userform events. Since setting it to false also may interfere with other add-ins loaded on your system, I tend to use this:

– Define a public variable in a normal module called:

Public bDisableEvents as Boolean

Then at the top of any event code :
Sub Whatever()
If bDisableEvents then Exit sub
bDisableEvents =True
‘…
bDisableEvents =False
End Sub

Reply | Quote

If you want to break the code when a certain value, or expression is false then use:

Debug.Assert ExpressionToCheck

If ExpressionToCheck is false, then the code will break on the debug.assert line, if it’s true, the code will keep running.

Reply | Quote

Why not just create two macros, one that disables events and one that enables evens and assign them to buttons on a toolbar?

Reply | Quote