A simple debug mode for your Excel workbooks

    When creating a worksheet that needs to be filled in by a client/end user to collect data, you might want to require certain fields/cells to be populated. In other words, there may be certain cells like "Client Name" that need to be filled in, in order for the worksheet to be further processed (either by code or manually). We'll want to prevent end users from submitting an incomplete worksheet to us by making certain cells required.

    There are several workbook events that are cancellable, for example:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
End Sub

Private Sub Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
End Sub

    In these events, we can do some validation on the worksheet and set "Cancel = True" in order to stop the action from being performed. In workbooks like these, the BeforeClose or BeforeSave Events are what I usually use to make sure the worksheet was filled out properly. Anyone who tries to save or close the workbook would be prevented from doing so, until they filled out whatever was required (as specified in the VBA code).

    For example, if you had a "Client Name" cell that the end user needed to fill in, the code would look something like this:

    Ideally, the cells would be named, to make our code easier to read and avoid hard-coding:

    Over time we might develop some complex validation rules. There might be dozens of cells that need to be filled in, and each cell might be checked for proper data types, using functions like IsNumeric and IsDate, or even <> "". This poses a problem if you use the BeforeSave Event to validate the data, because you as a developer will be stopped from saving the workbook without actually filling in all the fields!

    For example, if you took the worksheet and wanted to make some cosmetic changes, the validation in the BeforeSave Event would stop you from saving the changes unless you actually filled out the worksheet the way an end user would.

    When we're testing and developing our workbook, we don't want the validation to run and stop us from saving or editing it. Here is a simple way to toggle the validation behavior so it only runs when we want it to.

    I'll use a checkbox on the worksheet to toggle the edit mode of the worksheet. When checked, the validation rules will be skipped. When unchecked, the worksheet will be "live" and all of the validation rules will operate.

    First we'll open the Control Toolbox by going to View > Toolbars > Control Toolbox. You can also right-click any menu bar and choose "Control Toolbox" from the context menu.

    Select the Check Box Control and draw it on the worksheet. Right-click it and choose "Properties". Change the Caption Property to whatever you want; I use "Debug Mode". Also change the Name Property using the standard VB naming convention; I use 'chkDebug' in the examples below. As long as the name and the caption are consistent with each other, so you know which code goes with which button.

    Press Alt-F11 to go to Excel's VBIDE. If you don't already have a standard module in the workbook, go to Insert > Module and paste this code in:

    This is a Public Boolean variable that will store the value of our Checkbox.

    Right-click on the Checkbox again and choose "View Code". This should take you to an auto-created Click Event sub:

    Type the following so the sub looks like this:

    Or you can simply paste the above into the sheet module for the worksheet you are working with. This code will set the value of the Boolean variable to whatever the current state of the Checkbox is. So when you check it, gbDEBUG_MODE will be True, and when you uncheck it, gbDEBUG_MODE will be false.

    The next sub we need should be pasted into the ThisWorkbook module:

    This sub will check the state of the Checkbox when the workbook is first opened. That way, we don't have to toggle the Checkbox off and on in order to "reset" the value of the Boolean variable when we first open our workbook.

(Unfortunately one side-effect of this code is that now, end users will get the macro security warning when they first open the workbook. However if you were already using VBA code in it, they were probably getting the security warning anyway, so it shouldn't matter. Good end user training that addresses macro security issues should also be employed to make sure macros are allowed to run when the workbook opens.)

    Now all that is left to do is add Workbook-level events, or edit the existing ones, to check if our worksheet Checkbox is checked, before running any of the code within. Let's use the previous example in the BeforeSave event. Originally it was:

    Now we need to check the value of gbDEBUG_MODE, because we don't want our validation code to stop us from saving the workbook.

    Add all your other validation code here. When someone tries to save, if the Checkbox is checked (True), the code won't run. When you want to test out the code behind the worksheet, just uncheck the box (False). It will only run when the box is unchecked. So if you check the box and try to save, you can do so without the ClientName named range being filled in. Just uncheck the box and try to save, and you will get the error message, because the worksheet is now "live" and any code in the BeforeSave event will now run.

    You can also use gbDEBUG_MODE in other areas of your code to check if you are in developer mode before your code takes some action.

    For greatest effect, the checkbox should be on a hidden sheet so the end user can't simply check it themselves and get around the validation.

Enjoy,
JP

↑ Scroll to top
Previous Post: Excel Class Scheduler
Next Post: Send links via Outlook email