VBE Options: Disabling Debug Option on All Errors

[JGS]

Hi All

Excel's VBE (Visual Basic Editor) can be set to break on all errors
when the VBE encounters handled and unhandled errors when running a VBA
Add-in. This then gives the users the option to debug the code. I want
to avoid that if possible and know it is possible to do it because I
have seen Excel Add-ins that dim out the debug option and give the
users only the ability to End not Debug.

One messy option is to progam in the keystrokes to manually set the
VBE's Error-Handling Option to Break on Unhandled Errors." This is
messy and unreliable.

Another option is to remove all errors from the code. In my case, there
are some potential errors in my code but these errors are handled with
"On Error Resume Next" statements. I have them because there are some
VBA functions that I use in my code that are not present in every
version of Excel (eg CalculateFullRebuild). The error handling means I
can use those VBA functions safely knowing the error handling routine
will solve the problem for older versions of Excel. Unfortunately, if
the VBE is set to break on all errors I still have the problem that
users will have the option to debug the code.

Which brings me to the last possible solution that was suggested on
this forum by JE McGimpsey on 19 August 2004 in the following
terms:"Another correct answer is that if you simply close the file and
reopen it, the Debug option will be dimmed and your users will only
have the choice of End." Can anyone shed more light on this solution
and how you implement it?

Thanks

Jeremy

 

[Jim Rech]

I have seen Excel Add-ins that dim out the debug option and give the users
That's likely because the VB project has been password protected under
Tools, Options in the VBE.

 

[JGS]

Thanks Jim - That's exactly the answer - I had lock for viewing off to
avoid having to always enter the password during development - Regards
Jeremy