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
>>only the ability to End not Debug.

That's likely because the VB project has been password protected under
Tools, Options in the VBE.

--
Jim
"JGS" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> JGS
> ------------------------------------------------------------------------
> JGS's Profile:
> http://www.excelforum.com/member.php...o&userid=29337
> View this thread: http://www.excelforum.com/showthread...hreadid=490504
>

[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