worksheet-level defined names on each worksheet. In the “Practical Example” section later in the chapter we demonstrate how to create a procedure that applies these settings to the user interface workbook. This procedure will be called as part of the application startup code so that the settings are always applied by the time the user sees the user interface workbook. The MUtility module provided with the sample application also contains a procedure that automatically removes all settings from the user interface workbook to make it easier to maintain.
Using VBA to Dynamically Modify Your Worksheet
User Interface
There are many ways you can leverage the power of VBA to improve your user interface. Many of them, including techniques such as context- specific command bar enabling and dynamic hiding and unhiding of rows and columns, require the use of Excel event trapping that we cover in Chapter 7.
A simple example that we add to our sample application in this chap- ter is a feature that clears the data entry cells on a worksheet. A one-click method for clearing all input cells on the current user-interface work- sheet is often helpful to users. To do this, simply create a named range on each data entry worksheet that includes all the data input cells and give it an obvious name such as rgnClearInputs. This must be a sheet-level defined name created on all data entry worksheets in your workbook. Listing 5-4 shows the VBA implementation of our clear data entry area feature.
Listing 5-4 VBA Implementation of a Clear Data Entry Area Feature
Public Sub ClearDataEntryAreas() Dim rngToClear As Range
‘ Make sure the active worksheet has the rgnClearInputs ‘ defined name (i.e. it’s an input worksheet).
On Error Resume Next
Set rngToClear = ActiveSheet.Range(“rgnClearInputs”) On Error GoTo 0
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS
‘ of the input area.
If Not rngToClear Is Nothing Then rngToClear.ClearContents End Sub
Practical Example
We illustrate some of the concepts presented in this chapter by creating an application-specific add-in for the Professional Excel Timesheet Reporting and Analysis System (PETRAS) Time Sheet application that began as a sin- gle Excel workbook in Chapter 4.
Features
The add-in for our PETRAS time sheet application will perform the fol- lowing operations:
■ Open and initialize the application
■ Build a toolbar that gives the user access to each feature of the
application
■ Open and initialize the time entry workbook
■ Allow the user to save a copy of the time entry workbook to a pre- defined consolidation location
■ Allow the user to add more data entry rows to the time entry worksheet
■ Allow the user to clear the data entry area so the time sheet can eas-
ily be reused
■ Allow the user to close the PETRAS application
■ Add a custom property that allows the consolidation application to locate all instances of our time entry workbook
Let’s look at how the add-in accomplishes these tasks. We assume the WriteSettings utility procedure shown in Listing 5-3 has been run on the time entry workbook and the settings saved prior to running the add-in.
Open and Initialize the Application
The first operation the add-in performs when it is opened is to initialize the application and then open and initialize the user interface workbook. This is accomplished by the Auto_Open procedure, shown in Listing 5-5.
Listing 5-5 The PETRAS Add-in Auto_Open Procedure
Public Sub Auto_Open() Dim wkbBook As Workbook
‘ The very first thing your application should do upon ‘ startup is attempt to delete any copies of its ‘ command bars that may have been left hanging around ‘ by an Excel crash or other incomplete exit.
On Error Resume Next
Application.CommandBars(gsBAR_TOOLBAR).Delete On Error GoTo 0
‘ Initialize global variables. InitGlobals
‘ Make sure we can locate our time entry workbook before we ‘ do anything else.
If Len(Dir$(gsAppDir & gsFILE_TIME_ENTRY)) > 0 Then Application.ScreenUpdating = False
Application.StatusBar = gsSTATUS_LOADING_APP ‘ Build the command bars.
BuildCommandBars
‘ Determine if the time entry workbook is already open. ‘ If not, open it. If so, activate it.
On Error Resume Next
Set wkbBook = Application.Workbooks(gsFILE_TIME_ENTRY) On Error GoTo 0
If wkbBook Is Nothing Then
Set wkbBook = Application.Workbooks.Open( _ gsAppDir & gsFILE_TIME_ENTRY) Else
wkbBook.Activate End If
‘ Make the worksheet settings for the time entry ‘ workbook
MakeWorksheetSettings wkbBook
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS ResetAppProperties Else
MsgBox gsERR_FILE_NOT_FOUND, vbCritical, gsAPP_NAME ShutdownApplication
End If End Sub
The first thing the add-in does is blindly attempt to delete any previous instance of its toolbar. This should be considered a best practice. Application toolbars can be left behind due to an incomplete shutdown, which will then cause an error when your code tries to create them again the next time your application is run. Next, the add-in initializes any global variables. In this case we have two: a variable that holds the full path where the add-in is located and a variable that indicates when the add-in is in the process of shutting down.
As we mentioned in Chapter 3, you should use as few global variables as possible. When you do use them you must make sure they are in a known state at the beginning of every procedure where they might be accessed. Encapsulating this logic in an InitGlobals procedure that can be called wherever it’s needed is a good way to manage this process.
After the add-in has performed these two basic tasks it checks to see if it can locate the user interface workbook. If the user interface workbook is located, execution continues. Otherwise, an error message is displayed and the application exits. This makes sense because there is nothing the add-in can do without the user interface workbook.
Build a Toolbar That Gives the User Access to Each Feature
Next the add-in builds its toolbar. We accomplish this with basic, hard- coded VBA command bar building techniques that should be familiar to all readers of this book. Therefore, we don’t go into any detail on them. We cover more complex command bar building techniques in Chapter 8, “Advanced Command Bar Handling.”
The add-in exposes four distinct features to the user through the appli- cation toolbar, as shown in Figure 5-4. Each of these features is discussed in the sections that follow.
Open and Initialize the Time Entry Workbook
After the command bars have been built, the add-in checks to see if the user interface workbook is open. If this workbook is not open the Auto_Open procedure opens it. If this workbook is already open the Auto_Open procedure activates it. The next step is to initializes the user interface workbook. During this process all the settings that were saved to the user interface worksheets by the WriteSettings procedure in Listing 5-3 are read and applied by the MakeWorksheetSettings procedure. This pro- cedure is shown in Listing 5-6.
Listing 5-6 The MakeWorksheetSettings Procedure
Public Sub MakeWorksheetSettings(ByRef wkbBook As Workbook) Dim rngCell As Range
Dim rngSettingList As Range Dim rngHideCols As Range Dim sTabName As String Dim vSetting As Variant Dim wksSheet As Worksheet
Set rngSettingList = wksUISettings.Range(gsRNG_NAME_LIST) For Each wksSheet In wkbBook.Worksheets
‘ The worksheet must be unprotected and visible in order ‘ to make many of the settings. It will be protected and ‘ hidden again automatically by the settings code if it ‘ needs to be protected and/or hidden.
wksSheet.Unprotect
wksSheet.Visible = xlSheetVisible
‘ Hide any non-standard columns that need hiding. Set rngHideCols = Nothing
On Error Resume Next
Set rngHideCols = wksSheet.Range(gsRNG_SET_HIDE_COLS) On Error GoTo 0
If Not rngHideCols Is Nothing Then
rngHideCols.EntireColumn.Hidden = True End If
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS
‘ Determine if the current worksheet requires the ‘ current setting.
vSetting = Empty On Error Resume Next
If rngCell.Value = “setScrollArea” Then
‘ The scroll area setting must be treated ‘ differently because it’s a range object. Set vSetting = Application.Evaluate( _
“‘“ & wksSheet.Name & “‘!” & rngCell.Value) Else
vSetting = Application.Evaluate( _
“‘“ & wksSheet.Name & “‘!” & rngCell.Value) End If
On Error GoTo 0
If Not IsEmpty(vSetting) Then
If rngCell.Value = “setProgRows” Then If vSetting > 0 Then
wksSheet.Range(“A1”).Resize(vSetting) _ .EntireRow.Hidden = True
End If
ElseIf rngCell.Value = “setProgCols” Then If vSetting > 0 Then
wksSheet.Range(“A1”).Resize(, _
vSetting).EntireColumn.Hidden = True End If
ElseIf rngCell.Value = “setScrollArea” Then wksSheet.ScrollArea = vSetting.Address ElseIf rngCell.Value = “setEnableSelect” Then
wksSheet.EnableSelection = vSetting
ElseIf rngCell.Value = “setRowColHeaders” Then wksSheet.Activate
Application.ActiveWindow _ .DisplayHeadings = vSetting ElseIf rngCell.Value = “setVisible” Then
wksSheet.Visible = vSetting
ElseIf rngCell.Value = “setProtect” Then If vSetting Then
wksSheet.Protect , True, True, True End If
End If End If Next rngCell
Next wksSheet
‘ Leave the Time Entry worksheet active.
sTabName = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY) wkbBook.Worksheets(sTabName).Activate
End Sub
The MakeWorksheetSettings procedure loops through all the work- sheets in the specified workbook and applies all the settings that we defined for each worksheet. We designed this procedure to accept a refer- ence to a specific workbook object as an argument rather than having it assume it needs to operate on the user interface workbook because this design will allow us to generalize the application to handle multiple user interface workbooks if we need to at some time in the future. The settings table on which these settings were defined can be seen on the wksUISettings worksheet of the PetrasAddin.xla workbook.
After the user interface workbook has been initialized, the last thing we do is run a procedure that ensures all Excel application properties are set to their default values. This is the ResetAppProperties procedure shown in Listing 5-7.
Listing 5-7 The ResetAppProperties Procedure
Public Sub ResetAppProperties() Application.StatusBar = False Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True Application.EnableCancelKey = xlInterrupt Application.Cursor = xlDefault End Sub
This procedure is useful because we can make whatever application set- tings we like during the code execution required for a feature, and as long as we call this procedure before we exit we know that all critical applica- tion properties will be left in known good states. If we didn’t happen to use one of the properties reset by this procedure it doesn’t matter. The values set by the ResetAppProperties procedure are the default values for each property. Therefore we aren’t changing them if they weren’t used.
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS
Save a Copy of the Time Entry Workbook to a Predefined Consolidation Location
The first toolbar button saves a copy of the time entry workbook to a cen- tralized consolidation location. The procedure that implements this fea- ture is shown in Listing 5-8. From here, a procedure in the PETRAS Reporting Application consolidates the time entry workbooks from all of the consultants into a single report.
Listing 5-8 The PostTimeEntriesToNetwork Procedure
Public Sub PostTimeEntriesToNetwork() Dim sSheetTab As String
Dim sWeekEndDate As String Dim sEmployee As String Dim sSaveName As String Dim sSavePath As String Dim wksSheet As Worksheet Dim wkbBook As Workbook Dim vFullName As Variant
‘ Don’t do anything unless our time entry workbook is active ‘ wkbBook will return a reference to it if it is.
If bIsTimeEntryBookActive(wkbBook) Then
‘ Make sure the TimeEntry worksheet does not have any ‘ data entry errors.
sSheetTab = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY) Set wksSheet = wkbBook.Worksheets(sSheetTab)
If wksSheet.Range(gsRNG_HAS_ERRORS).Value Then MsgBox gsERR_DATA_ENTRY, vbCritical, gsAPP_NAME Exit Sub
End If
‘ Create a unique name for the time entry workbook. sWeekEndDate = Format$( _
wksSheet.Range(gsRNG_WEEK_END_DATE).Value, _ “YYYYMMDD”)
sEmployee = wksSheet.Range(gsRNG_EMPLOYEE_NAME).Value sSaveName = sWeekEndDate & “ - “ & sEmployee & “.xls” ‘ Check the registry to determine if we already have a
‘ consolidation path specified. If so, save the time ‘ entry workbook to that location. If not, prompt the ‘ user to identify a consolidation location, save that ‘ location to the registry and save the time entry ‘ workbook to that location.
sSavePath = GetSetting(gsREG_APP, gsREG_SECTION, _ gsREG_KEY, ““)
If Len(sSavePath) = 0 Then
‘ No path was stored in the registry. Prompt the ‘ user for one.
vFullName = Application.GetOpenFilename( _ Title:=gsCAPTION_SELECT_FOLDER) If vFullName <> False Then
‘ NOTE: The InStrRev function was not available ‘ in Excel 97.
sSavePath = Left$(vFullName, _ InStrRev(vFullName, “\”))
SaveSetting gsREG_APP, gsREG_SECTION, _ gsREG_KEY, sSavePath
Else
‘ The user cancelled the dialog.
MsgBox gsMSG_POST_FAIL, vbCritical, gsAPP_NAME Exit Sub
End If End If
wkbBook.SaveCopyAs sSavePath & sSaveName
MsgBox gsMSG_POST_SUCCESS, vbInformation, gsAPP_NAME Else
MsgBox gsMSG_BOOK_NOT_ACTIVE, vbExclamation, gsAPP_NAME End If
End Sub
This procedure shows the safety mechanism we use to prevent runtime errors from occurring if the user clicks one of our toolbar buttons without the user interface workbook being active. Prior to performing any action we verify that this workbook is active using the bIsTimeEntryBookActive() function. This function returns True if the time entry workbook is active and False if it is not. If the time entry workbook is active, the function also returns an object reference to the time entry workbook via its ByRef Workbook argument. If the time entry workbook is not active we display an error message to the user and exit.
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS
Once we verify the time entry workbook is active we check the error flag in the hidden column on the time entry worksheet to determine if the time sheet has any data entry errors. If the flag indicates there are errors we dis- play a message to the user and exit. If there are no data entry errors, the next task is to create a unique name for the workbook and look for our consoli- dation path in the registry. If the consolidation path has not yet been saved to the registry, we prompt the user to specify the path that should be used. Finally, we use the SaveCopyAs method of the Workbook object to post a copy of the workbook to the central consolidation location. We then display a message to the user indicating that the process succeeded.
Allow the User to Add More Data Entry Rows to the Time Entry Worksheet
In the version of the time entry workbook demonstrated in Chapter 4, the number of data entry rows was fixed. In this version, the second toolbar but- ton allows the user to add additional rows to the time entry table as needed. The procedure that implements this feature is shown in Listing 5-9. Listing 5-9 The AddMoreRows Procedure
Public Sub AddMoreRows()
Const lOFFSET_COLS As Long = 5 Const lINPUT_COLS As Long = 6 Dim rngInsert As Range
Dim wkbBook As Workbook Dim wksSheet As Worksheet
‘ Don’t do anything unless our time entry workbook is active If bIsTimeEntryBookActive(wkbBook) Then
‘ Get a reference to the TimeEntry worksheet and the ‘ insert row range on it. All new rows will be inserted ‘ above this range.
Set wksSheet = wkbBook.Worksheets(sSheetTabName( _ wkbBook, gsSHEET_TIME_ENTRY)) Set rngInsert = wksSheet.Range(gsRNG_INSERT_ROW)
‘ Add a new row to the time entry table. wksSheet.Unprotect
wksSheet.ScrollArea = ““ rngInsert.EntireRow.Insert
rngInsert.Offset(-2, 0).EntireRow.Copy _ Destination:=rngInsert.Offset(-1, 0) rngInsert.Offset(-1, lOFFSET_COLS) _ .Resize(1, lINPUT_COLS).ClearContents wksSheet.ScrollArea = _ wksSheet.Range(gsRNG_SET_SCROLL_AREA).Address wksSheet.Protect , True, True, True
Else
MsgBox gsMSG_BOOK_NOT_ACTIVE, vbExclamation, gsAPP_NAME End If
End Sub
In the AddMoreRows procedure we use the same method to determine if a time entry workbook is active as we used in the PostTimeEntriesToNetwork procedure. Once we’ve determined we have a valid workbook active, inserting a new row is a three-step process:
1. Insert a new row directly above the last row in the table. The last row in the table is marked by the gsRNG_INSERT_ROW defined name. 2. Copy the row above the newly inserted row and paste it onto the newly inserted row. This ensures all functions, formatting, and val- idation required to make the table operate and appear correctly are transferred to the newly inserted row.
3. The contents of the data entry area of the newly inserted row is cleared of any data that may have been transferred to the new row by the previous step. The new data entry row is now clean and ready to be used.
We remove the scroll area setting from the worksheet prior to inserting the new row and add it back after the new row has been inserted. If we didn’t do this the worksheet scroll area would not adjust correctly as additional rows were inserted into the table.
Allow the User to Clear the Data Entry Area so the Time Sheet Can Be Reused
The third toolbar button, Clear Data Entries, simply clears the values from all of the data entry areas on the time sheet. The code to implement this feature was discussed in the “Using VBA to Dynamically Modify Your Worksheet User Interface” section earlier in the chapter, so we won’t repeat it here.
5. F UNCTION , G ENERAL , AND A PPLICA TION -S PECIFIC A DD - INS
Allow the User to Close the PETRAS Application
The fourth and last toolbar button simply closes the PETRAS application workbooks and removes its toolbar. The ExitApplication procedure that implements this feature is shown in Listing 5-10.
Listing 5-10 The ExitApplication Procedure
Public Sub ExitApplication() ShutdownApplication End Sub
This is a one-line stub procedure that simply calls the ShutdownApplication procedure, which actually performs the tasks required to shut down the application. We place the shutdown logic in a separate procedure because it must be called from the ExitApplication procedure as well as from the Auto_Close procedure. These two proce- dures reflect the two ways the user could exit our application: selecting the Exit PETRAS toolbar button or using one of Excel’s built-in exit features. The code for the ShutdownApplication procedure is shown in Listing 5-11. Listing 5-11 The ShutdownApplication Procedure
Public Sub ShutdownApplication()
‘ Ignore any errors on application shutdown. On Error Resume Next
‘ This flag prevents this procedure from being called a ‘ second time by Auto_Close if it has already been called ‘ by the ExitApplication procedure.
gbShutdownInProgress = True ‘ Delete command bar.
Application.CommandBars(gsBAR_TOOLBAR).Delete
‘ Close the time entry workbook, allowing the user to ‘ save changes.
Application.Workbooks(gsFILE_TIME_ENTRY).Close ‘ If there are no workbooks left open, quit Excel ‘ Otherwise just close this workbook.
If lCountVisibleWorkbooks() = 0 Then ThisWorkbook.Saved = True
Application.Quit Else
ThisWorkbook.Close False End If
End Sub
The ShutdownApplication procedure is an example of a procedure where you want to ignore any errors that might occur during code execution. The application is closing down, so there isn’t anything useful that could be done about any errors that did occur. Therefore, we tell VBA to ignore any