In This Chapter
If you develop a nontrivial application in Excel, you may want to consider building in some sort of help for end users. Doing so makes the users feel more comfortable with the application and could eliminate many of those time-wasting phone calls from users with basic questions. Another advantage is that help is always available: That is, the instructions for using your application can’t be misplaced or buried under a pile of books.
You can provide help for your Excel applications in a number of ways, ranging from simple to complex. The method that you choose depends on your application’s scope and complexity and how much effort you’re willing to put into this phase of development. Some applications might require only a brief set of instructions on how to start them. Others may benefit from a full-blown searchable Help system. Most often, applications need something in between.
This chapter classifies user help into two categories:
Creating a compiled help file isn’t a trivial task, but it is worth the effort if your application is complex or if it will be used by a large number of people.
Perhaps the most straightforward method of providing help to your users is to use the features in Excel itself. The primary advantage of this method is that you don’t need to learn how to create HTML help files — which can be a major undertaking and might take longer to develop than your application.
In this section, I provide an overview of some help techniques that use the following built-in Excel components:
Perhaps the simplest way to provide user help is to use cell comments. This technique is most appropriate for describing the type of input that’s expected in a cell. When the user moves the mouse pointer over a cell that contains a comment, the comment appears in a small window, like a tooltip (see Figure 19.1). Another advantage is that this technique doesn’t require macros.
The automatic display of cell comments is an option. The following VBA instruction, which can be placed in a Workbook_Open procedure, ensures that cell comment indicators are displayed for cells that contain comments:
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
As an alternative to cell comments, you can use Excel’s Data ➜ Data Tools ➜ Data Validation command, which displays a dialog box that lets you specify validation criteria for a cell or range. You can just ignore the data validation aspect and use the Input Message tab of the Data Validation dialog box to specify a message that’s displayed when the cell is activated. This text is limited to 255 characters.
Using a text box to display help information is also easy to implement. Simply create a text box by choosing Insert ➜ Text ➜ Text Box, enter the help text, and format it to your liking.
Figure 19.2 shows an example of a shape set up to display help information. I added a shadow effect to make the object appear to float above the worksheet.
Most of the time, you won’t want the text box to be visible. Therefore, you can add a button to your application to execute a macro that toggles the Visible property of the text box. An example of such a macro follows. In this case, the TextBox is named HelpText.
Sub ToggleHelp() ActiveSheet.TextBoxes("HelpText").Visible = _ Not ActiveSheet.TextBoxes("HelpText").Visible End Sub
Another easy way to add help to your application is to create a macro that activates a separate worksheet that holds the help information. Just attach the macro to a button control and — voilà! — quick-and-dirty help.
Figure 19.3 shows a sample help worksheet. I designed the range that contains the help text to simulate a page from a yellow notebook pad — a touch that you may or may not like.
To keep the user from scrolling around the HelpSheet worksheet, the macro sets the ScrollArea property of the worksheet. Because this property isn’t stored with the workbook, it must be set when the worksheet is activated.
Sub ShowHelp() ' Activate help sheet Worksheets("HelpSheet").Activate ActiveSheet.ScrollArea ="A1:C35" Range("A1").Select End Sub
I also protected the worksheet to prevent the user from changing the text and selecting cells, and I froze the first row so that the Return to the Form button is always visible, regardless of how far down the sheet the user scrolls.
The main disadvantage of using this technique is that the help text isn’t visible along with the main work area. One possible solution is to write a macro that opens a new window to display the sheet.
Another way to provide help to the user is to display the text in a UserForm. In this section, I describe several techniques that involve UserForms.
Figure 19.4 shows a UserForm that contains two Label controls: one for the title and one for the help text. A SpinButton control enables the user to navigate among the topics. The text itself is stored in a worksheet, with topics in column A and text in column B. A macro transfers the text from the worksheet to the Label controls.
Clicking the SpinButton control executes the following procedure. This procedure sets the Caption property of the two Label controls to the text in the appropriate row of the worksheet (named HelpSheet).
Private Sub sbTopics_Change() HelpTopic = Me.sbTopics.Value Me.lblTitle.Caption = _ Sheets("HelpSheet").Cells(HelpTopic, 1).Value Me.lblTopic.Caption = _ Sheets("HelpSheet").Cells(HelpTopic, 2).Value Me.Caption = APPNAME &" (Help Topic" & HelpTopic &" of" _ & Me.sbTopics.Max &")" End Sub
Here, APPNAME is a global constant that contains the application’s name.
The next technique displays help text in a single Label control. Because a Label control can’t contain a vertical scroll bar, the Label is placed inside a Frame control, which can contain a scroll bar. Figure 19.5 shows an example of a UserForm set up in this manner. The user can scroll through the text by using the Frame’s scroll bar.
The text displayed in the Label is read from a worksheet named HelpSheet when the UserForm is initialized. Here’s the UserForm_Initialize procedure for this worksheet:
Private Sub UserForm_Initialize() Dim LastRow As Long Dim r As Long Dim txt As String Me.Caption = APPNAME &" Help" LastRow = Sheets("HelpSheet").Cells(Rows.Count, 1).End(xlUp).Row txt ="" For r = 1 To LastRow txt = txt & Sheets("HelpSheet").Cells(r, 1).Text & vbCrLf Next r With Me.lblMain .Top = 0 .Caption = txt .Width = 260 .AutoSize = True End With Me.frmMain.ScrollHeight = Me.lblMain.Height Me.frmMain.ScrollTop = 0 End Sub
Note that the code adjusts the Frame’s ScrollHeight property to ensure that the scrolling covers the complete height of the Label. Again, APPNAME is a global constant that contains the application’s name.
Because a Label can’t display formatted text, I used underscore characters in the HelpSheet worksheet to delineate the help topic titles.
The example in this section improves upon the preceding example. Figure 19.6 shows a UserForm that contains a ComboBox control and a Label control. The user can select a topic from the drop-down ComboBox or view the topics sequentially by clicking the Previous or Next button.
This example is a bit more complex than the example in the preceding section, but it’s also much more flexible. It uses the label-within-a-scrolling-frame technique (described previously) to support help text of any length.
The help text is stored in a worksheet named HelpSheet in two columns (A and B). The first column contains the topic headings, and the second column contains the text. The ComboBox items are added in the UserForm_Initialize procedure. The CurrentTopic variable is a module-level variable that stores an integer that represents the help topic.
Private Sub UpdateForm() Me.cbxTopics.ListIndex = CurrentTopic - 1 Me.Caption = APPNAME & _ " (" & CurrentTopic &" of" & TopicCount &")" With Me.lblMain .Caption = HelpSheet.Cells(CurrentTopic, 2).Value .AutoSize = False .Width = 212 .AutoSize = True End With With Me.frmMain .ScrollHeight = Me.lblMain.Height + 5 .ScrollTop = 1 End With If CurrentTopic = 1 Then Me.cmdNext.SetFocus ElseIf CurrentTopic > TopicCount Then Me.cmdPrevious.SetFocus End If Me.cmdPrevious.Enabled = CurrentTopic > 1 Me.cmdNext.Enabled = CurrentTopic < TopicCount End Sub
This section describes two ways to display user help in a web browser.
Yet another way to display help for an Excel application is to create one or more HTML files and provide a hyperlink that displays the file in the default web browser. The HTML files can be stored locally or on your corporate intranet. You can create the hyperlink to the help file in a cell (macros not required). Figure 19.7 shows an example of help in a browser.
Easy-to-use HTML editors are readily available, and your HTML-based Help system can be as simple or as complex as necessary. A disadvantage is that you may need to distribute a large number of HTML files. One solution to this problem is to use an MHTML file, which I describe next.
MHTML, which stands for MIME Hypertext Markup Language, is a web archive format. MHTML files can be displayed by Microsoft Internet Explorer (and a few other browsers).
The nice thing about using an MHTML file for an Excel Help system is that you can create these files in Excel. Just create your help text using any number of worksheets. Then choose File ➜ Save As, click the Save As Type drop-down list, and select Single File Web Page (*.mht; *.mhtml). VBA macros aren’t saved in this format.
In Excel, you can create a hyperlink to display the MHTML file.
Figure 19.8 shows an MHTML file displayed in Internet Explorer. Note that the bottom of the file contains tabs that link to the help topics. These tabs correspond to the worksheet tabs in the Excel workbook used to create the MHTML file.
One of the most common Help systems used in Windows applications is HTML Help, which creates CHM files. This system replaces the old Windows Help system (WinHelp), which used HLP files. Both Help systems enable the developer to associate a context ID with a particular help topic, which makes it possible to display context-sensitive help topics.
Office XP was the last version of Microsoft Office to use HTML Help. Although HTML Help can’t duplicate the look and feel of Microsoft Office Help, it is still useful because it’s easy to work with — at least for simple Help systems.
In this section I briefly describe the HTML help-authoring system. Details on creating such Help systems are well beyond the scope of this book. However, you’ll find lots of information and examples online.
A compiled HTML Help system transforms a series of HTML files into a compact Help system. Additionally, you can create a combined table of contents and index as well as use keywords for advanced hyperlinking capability. HTML Help can also use additional tools such as graphics files, ActiveX controls, scripting, and DHTML (Dynamic HTML). Figure 19.9 shows an example of a simple HTML Help system.
HTML Help is displayed by HTML Help Viewer, which uses the layout engine of Internet Explorer. The information is displayed in a window, and the table of contents, index, and search tools are displayed in a separate pane. In addition, the help text can contain standard hyperlinks that display another topic or even a document on the Internet. It’s also important that HTML Help can access files stored on a website, so that you can direct users to more up-to-date information.
You need a special compiler (HTML Help Workshop) to create an HTML Help system. HTML Help Workshop, along with lots of additional information, is available free from Microsoft’s MSDN website. Navigate to this address and search for HTML Help Workshop: http://msdn.microsoft.com.
Figure 19.10 shows HTML Help Workshop with the project file that created the Help system shown in Figure 19.9.
Use the Help method of the Application object to display a help file — either a WinHelp HLP file or an HTML Help CHM file. This method works even if the help file doesn’t have context IDs defined.
The syntax for the Help method is as follows:
Application.Help(helpFile, helpContextID)
Both arguments are optional. If the name of the help file is omitted, Excel’s help file is displayed. If the context ID argument is omitted, the specified help file is displayed with the default topic.
The following example displays the default topic of myapp.chm, which is assumed to be in the same directory as the workbook from which it’s called. Note that the second argument is omitted.
Sub ShowHelpContents() Application.Help ThisWorkbook.Path &"myapp.chm" End Sub
The following instruction displays the help topic with a context ID of 1002 from an HTML help file named myapp.chm:
Application.Help ThisWorkbook.Path &"myapp.chm", 1002
You can associate a particular HTML help file with your Excel application in one of two ways: by using the Project Properties dialog box or by writing VBA code.
In Visual Basic Editor (VBE), choose Tools ➜ xxx Properties (where xxx corresponds to your project’s name). In the Project Properties dialog box, click the General tab and specify a compiled HTML help file for the project. This file should have a .chm extension.
The statement that follows demonstrates how to associate a help file with your application by using a VBA statement. The following instruction sets up an association to myfuncs.chm, which is assumed to be in the same directory as the workbook:
ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path &"myfuncs.chm"
When a help file is associated with your application, you can call up a particular help topic in the following situations:
If you create custom worksheet functions with VBA, you might want to associate a help file and context ID with each function. After these items are assigned to a function, the help topic can be displayed from the Insert Function dialog box by pressing F1.
To specify a context ID for a custom worksheet function, follow these steps:
Right-click the function and then select Properties from the shortcut menu.
The Member Options dialog box is displayed, as shown in Figure 19.11.
Enter the context ID of the help topic for the function.
You can also enter a description of the function.
You may prefer to write VBA code that sets up the context ID and help file for your custom functions. You can do this by using the MacroOptions method.
The following procedure uses the MacroOptions method to specify a description, help file, and context ID for two custom functions (AddTwo and Squared). You need to execute this macro only one time.
Sub SetOptions() ' Set options for the AddTwo function Application.MacroOptions Macro:="AddTwo", _ Description:="Returns the sum of two numbers", _ HelpFile:=ThisWorkbook.Path &"myfuncs.chm", _ HelpContextID:=1000, _ ArgumentDescriptions:=Array("The first number to add", _ "The second number to add") ' Set options for the Squared function Application.MacroOptions Macro:="Squared", _ Description:="Returns the square of an argument", _ HelpFile:=ThisWorkbook.Path &"myfuncs.chm", _ HelpContextID:=2000, _ ArgumentDescriptions:=Array("The number to be squared") End Sub
After executing these procedures, the user can get help directly from the Insert Function dialog box by clicking the Help on This Function hyperlink.
13.58.21.119