In this section, we explain how to create a visually pleasing background for use in our dictator application when no documents are open. We then dis- cuss the considerations surrounding the choice between a sheet-based and form-based user interface for our application.
Preparing a Backdrop Graphic
At this point, we have an empty, locked-down screen ready for us to add our application’s user interface. The first UI element to add typically is some sort of background graphic to display as our application’s desktop. The simplest version of this is to have a single worksheet contained in our application add-in that is copied to a new, visible workbook. The workbook is then maximized, has the appropriate worksheet display attributes set, and the display range is zoomed to fill the Excel window, as shown in Listing 6-6. The workbook windows can then be protected to remove the control box and minimize/maximize buttons.
6. D ICT ATOR A PPLICA TIONS
Listing 6-6 Code to Prepare a Background Graphic Workbook
Public gwbkBackDrop As Workbook
Public Const gsBACKDROP_TITLE As String = “BackdropWkbk” Sub PrepareBackDrop()
Dim wkbBook As Workbook
If Not WorkbookAlive(gwbkBackDrop) Then
‘See if there’s already a backdrop workbook out there Set gwbkBackDrop = Nothing
For Each wkbBook In Workbooks
If wkbBook.BuiltinDocumentProperties(“Title”) = _ gsBACKDROP_TITLE Then
Set gwbkBackDrop = wkbBook Exit For
End If Next
If gwbkBackDrop Is Nothing Then
‘Copy the backdrop sheet out of this workbook ‘into a new one for display
wksBackdrop.Copy
Set gwbkBackDrop = ActiveWorkbook
gwbkBackDrop.BuiltinDocumentProperties(“Title”) = _ gsBACKDROP_TITLE End If End If With gwbkBackDrop .Activate
‘Select the full region that encompasses the backdrop ‘graphic, so we can use Zoom = True to size it to fit .Worksheets(1).Range(“rgnBackDrop”).Select
‘Set the Window View options to hide everything With .Windows(1)
.WindowState = xlMaximized .Caption = ““
.DisplayVerticalScrollBar = False .DisplayHeadings = False
.DisplayWorkbookTabs = False
‘Zoom the selected area to fit the screen .Zoom = True
End With
‘Prevent selection or editing of any cells With .Worksheets(1) .Range(“ptrCursor”).Select .ScrollArea = .Range(“ptrCursor”).Address .EnableSelection = xlNoSelection .Protect DrawingObjects:=True, _ UserInterfaceOnly:=True End With
‘Protect the backdrop workbook, to remove the ‘control menu
.Protect Windows:=True .Saved = True
End With End Sub
‘Function to test if a given workbook object variable ‘points to a valid workbook
Function WorkbookAlive(wbkTest As Workbook) As Boolean On Error Resume Next
If Not wbkTest Is Nothing Then
WorkbookAlive = wbkTest.Sheets(1).Name <> ““ End If
End Function
A more complex version contains multiple potential backdrop sheets, each designed for a specific screen resolution or window size. At run- time the appropriate sheet is selected based on the window’s height or width. 6. D ICT ATOR A PPLICA TIONS
Sheet-Based Versus Form-Based User Interfaces
There are two primary styles of user interface for dictator applications: those that use worksheets for the main data entry forms and those that use UserForms. Both styles can be combined with a custom menu structure, though it is slightly harder with a form-based user interface.
Worksheet-based user interfaces are similar to the application-specific add-ins discussed in Chapter 5, “Function, General, and Application- Specific Add-ins,” and are designed to make maximum use of Excel’s edit- ing features, such as auto-complete, data validation, and conditional for- matting. While the use of Excel’s rich functionality is a compelling choice, you must take care to ensure the users do not accidentally destroy the data entry form. If you decide on a worksheet-based user interface, use work- sheets for all your major data entry forms and reports; dialogs should only be used for minor tasks and wizards.
Form-based user interfaces are typically found in applications that use Excel primarily for its calculation and analysis features rather than the rich editing experience. The data entry forms tend to be much simpler than those in a worksheet-based user interface. This can be a benefit for both the user and the developer because the reduced functionality and tighter control that UserForms provide results in less chance for users to make mistakes and therefore a more robust solution. If you decide to use a form- based user interface, worksheets should only be used for reporting. Designing a form-based user interface is covered in detail in Chapter 13, “UserForm Design and Best Practices.”
Trying to mix the two user interface styles rarely works well. It is sim- ply too cumbersome to make worksheets behave like UserForms (such as tabbing between controls) and vice versa (such as auto-complete). When deciding which style to use, base the decision on where the user is likely to spend the majority of his time. Will the user be better served by the rich editing features of a worksheet or the tighter control of a UserForm?
Handling Cut, Copy, and Paste The biggest issue with worksheet-based user interfaces is having to override Excel’s default handling of cut, copy, paste, and drag-and-drop. As discussed in Chapter 4, “Worksheet Design,” most of the editable cells in a data entry worksheet are given specific styles, data val- idation, and conditional formats. Unfortunately, Excel’s default copy/paste behavior overwrites the formatting of the destination cell, and Excel’s default cut behavior is to format the cell being cut with the Normal style, which is normally used for the worksheet background. Excel’s drag-and-drop feature is the same as cut and paste and also destroys the data entry sheet if used. The only way to avoid this is to switch off drag-and-drop and code our own cut, copy, and paste replacement procedures, such as those shown in Listing 6-7.
Listing 6-7 Code to Handle Cut, Copy, and Paste for Data Entry Worksheets
Dim mbCut As Boolean Dim mrngSource As Range ‘Initialise cell copy-paste Public Sub InitCutCopyPaste()
‘Hook all the cut, copy and paste keystrokes Application.OnKey “^X”, “DoCut”
Application.OnKey “^x”, “DoCut” Application.OnKey “+{DEL}”, “DoCut” Application.OnKey “^C”, “DoCopy” Application.OnKey “^c”, “DoCopy”
Application.OnKey “^{INSERT}”, “DoCopy” Application.OnKey “^V”, “DoPaste” Application.OnKey “^v”, “DoPaste”
Application.OnKey “+{INSERT}”, “DoPaste” Application.OnKey “{ENTER}”, “DoPaste” Application.OnKey “~”, “DoPaste” ‘Switch off drag/drop
Application.CellDragAndDrop = False End Sub
‘Handle Cutting cells Public Sub DoCut()
If TypeOf Selection Is Range Then mbCut = True
Set mrngSource = Selection Selection.Copy
Else
Set mrngSource = Nothing Selection.Cut
End If End Sub
‘Handle Copying cells Public Sub DoCopy()
6. D ICT ATOR A PPLICA TIONS
If TypeOf Selection Is Range Then mbCut = False
Set mrngSource = Selection Else
Set mrngSource = Nothing End If
Selection.Copy End Sub
‘Handle pasting cells Public Sub DoPaste()
If Application.CutCopyMode And Not mrngSource Is Nothing Then Selection.PasteSpecial xlValues If mbCut Then mrngSource.ClearContents End If Application.CutCopyMode = False Else ActiveSheet.Paste End If End Sub
Custom Command Bars
Most dictator applications include a set of menus and toolbars to provide access to the application’s functionality. Dictator applications usually have complex menu structures, mixing built-in Excel menu items (such as Print and Print Preview) with custom menu items. The maintenance of these menu items can be made significantly easier by using a table-driven approach to building the command bars, as discussed in Chapter 8, “Advanced Command Bar Handling.”