Chapter 24: Providing Help for Your Applications

IN THIS CHAPTER

• Providing user help for your applications

• Using only the components supplied with Excel to provide help

• Displaying help files created with the HTML Help system

• Associating a help file with your application

• Displaying HTML Help in other ways

Help for Your Excel Applications

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 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:

• Unofficial Help system: This method of displaying help uses standard Excel components (such as a UserForm).

• Official Help system: This Help system uses a compiled CHM file produced by Microsoft's HTML Help Workshop.

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.

Beginning with Microsoft Office 2007, Microsoft abandoned CHM help files in their Office product, and used a completely different (and much more complicated) Help system called MS Help 2. This Help system isn't covered in this book.

All the examples in this chapter are available on the companion CD-ROM. Because most examples consist of multiple files, each example is in a separate directory on the CD.

Help Systems That Use Excel Components

Perhaps the most straightforward method of providing help to your users is to use the features contained 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:

• Cell comments: Using comments is about as simple as it gets.

• A text box control: A short macro is all it takes to toggle the display of a text box that shows help information.

• A worksheet: An easy way to add help is to insert a worksheet, enter your help information, and name its tab Help. When the user clicks the tab, the worksheet is activated.

• A custom UserForm: A number of techniques involve displaying help text in a UserForm.

Using cell comments for help

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, that comment appears in a small window, like a ToolTip (see Figure 24-1). Another advantage is that this technique doesn't require any macros.

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

A workbook that demonstrates the use of cell comments is available on the companion CD-ROM. The filename is cell commentsformletter.xlsm.

Most users don't realize it, but a comment can also display an image. Right-click the comment's border and choose Format Comment from the shortcut menu. In the Format Comment dialog box, select the Colors and Lines tab. Click the Color drop-down list and select Fill Effects. In the Fill Effects dialog box, click the Picture tab and then click the Select Picture button to choose the image file.

Another option is to 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 approximately 250 characters.

FIGURE 24-1: Using cell comments to display help.

Using a text box for help

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.

In lieu of a text box, you can use a different shape and add text to it. Choose Insert⇒Illustrations⇒Shapes and choose a shape. Then just starting typing the text.

Figure 24-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

A workbook that demonstrates using a text box for help is available on the companion CD-ROM. The filename is textboxformletter.xlsm.

FIGURE 24-2: Using a shape object with text to display help for the user.

Using a worksheet to display help text

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 24-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 fancy 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's necessary to set it when the worksheet is activated. 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.

FIGURE 24-3: Putting user help in a separate worksheet is an easy way to go.

The companion CD-ROM contains a workbook named worksheetformletter.xlsm that demonstrates using a worksheet for help.

Displaying help in a UserForm

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.

Using Label controls to display help text

Figure 24-4 shows a UserForm that contains two Label controls: one for the title and one for the actual 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.

FIGURE 24-4: Clicking one of the arrows on the SpinButton changes the text displayed in the Labels.

Clicking the SpinButton control executes the following procedure. This procedure simply sets the Caption property of the two Label controls to the text in the appropriate row of the worksheet (named HelpSheet).

Private Sub SpinButton1_Change()

HelpTopic = SpinButton1.Value

LabelTopic.Caption = Sheets(“HelpSheet”). _

Cells(HelpTopic, 1)

LabelText.Caption = Sheets(“HelpSheet”).Cells(HelpTopic, 2)

Me.Caption = APPNAME & “ (Help Topic “ & HelpTopic & “ of “ _

& SpinButton1.Max & “)”

End Sub

Here, APPNAME is a global constant that contains the application's name.

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is userform1formletter.xlsm.

Using a scrolling Label to display help text

This 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 24-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.

FIGURE 24-5: Inserting a Label control inside a Frame control adds scrolling to the Label.

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 Label1

.Top = 0

.Caption = txt

.Width = 260

.AutoSize = True

End With

With Frame1

.ScrollHeight = Label1.Height

.ScrollTop = 0

End With

End Sub

Notice 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.

A workbook that demonstrates this technique is available on the companion CD-ROM as a file named userform2formletter.xlsm.

Using a ComboBox control to select a Help topic

The example in this section improves upon the previous example. Figure 24-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 previous 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.

FIGURE 24-6: Using a drop-down list control to select a Help topic.

Private Sub UpdateForm()

ComboBoxTopics.ListIndex = CurrentTopic - 1

Me.Caption = HelpFormCaption & _

“ (“ & CurrentTopic & “ of “ & TopicCount & “)”

With LabelText

.Caption = HelpSheet.Cells(CurrentTopic, 2)

.AutoSize = False

.Width = 212

.AutoSize = True

End With

With Frame1

.ScrollHeight = LabelText.Height + 5

.ScrollTop = 1

End With

If CurrentTopic = 1 Then

NextButton.SetFocus

ElseIf CurrentTopic = TopicCount Then

PreviousButton.SetFocus

End If

PreviousButton.Enabled = CurrentTopic <> 1

NextButton.Enabled = CurrentTopic <> TopicCount

End Sub

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is userform3formletter.xlsm.

Displaying Help in a Web Browser

This section describes two ways to display user help in a Web browser.

Using HTML files

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 24-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.

FIGURE 24-7: Displaying help in a Web browser.

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is web browserformletter.xlsm.

Using an MHTML file

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.

Figure 24-8 shows an MHTML file displayed in Internet Explorer.

Apparently, some versions of Internet Explorer won't display an MHTML file hyperlinked from Excel if the filename or path includes space characters.

In Excel, you can create a hyperlink to display the MHTML file.

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is mhtml_fileformletter.xlsm.

If you save a multisheet Excel workbook as an MHTML file, the file will contain JavaScript code — which may generate a security warning when the file is opened.

FIGURE 24-8: Displaying an MHTML file in a Web browser.

Using the HTML Help System

One of the most common Help systems used in Windows applications is HTML Help, which uses CHM files. This system replaces the old Windows Help system (WinHelp), which used Hlp files (see the sidebar, “Microsoft's Help system evolution”). Both of these Help systems enable the developer to associate a context ID with a particular Help topic. This makes it possible to display a particular Help topic in a context-sensitive manner.

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.

If you plan to develop a large-scale Help system, I strongly recommend that you purchase a help-authoring software product to make your job easier. Help-authoring software makes it much easier to develop help files because the software takes care of lots of the tedious details for you. Many products are available, including freeware, shareware, and commercial offerings.

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 24-9 shows an example of a simple HTML help system.

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is html helpformletter.xlsm.

FIGURE 24-9: An example of HTML Help.

HTML Help is displayed by the 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 Web site. This is ideal for directing users to a source of up-to-date information that might not have been available when the Help system was created.

You need a special compiler to create an HTML Help system. The HTML Help Workshop, along with lots of additional information, is available free from Microsoft's MSDN Web site. Navigate to this address and search for HTML Help Workshop:

http://msdn.microsoft.com

Figure 24-10 shows the HTML Help Workshop with the project file that created the Help system shown in Figure 24-9.

FIGURE 24-10: Using the HTML Help Workshop to create a help file.

Using the Help method to display HTML Help

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 any 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 that it's called from. 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

Associating a Help File with Your Application

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 the 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”

If this statement generates an error, you must enable programmatic access to VBA projects. In Excel, choose Developer⇒Code⇒Macro Security to display the Trust Center dialog box. Then uncheck the option labeled Trust Access to the VBA Project Object Model.

When a help file is associated with your application, you can call up a particular Help topic in the following situations:

• When the user presses F1 while a custom worksheet function is selected in the Insert Function dialog box.

• When the user presses F1 while a UserForm is displayed. The Help topic associated with the control that has the focus is displayed.

Associating a Help topic with a VBA function

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:

1. Create the function as usual.

2. Make sure that your project has an associated help file (refer to the preceding section).

3. In the VBE, press F2 to activate the Object Browser.

4. Select your project from the Project/Library drop-down list.

5. In the Classes window, select the module that contains your function.

6. In the Members Of window, select the function.

7. Right-click the function and then select Properties from the shortcut menu.

This displays the Member Options dialog box, shown in Figure 24-11.

FIGURE 24-11: Specify a context ID for a custom function in the Member Options dialog box.

8. Enter the context ID of the Help topic for the function.

You can also enter a description of the function.

The Member Options dialog box doesn't let you specify the help file. It always uses the help file associated with the project.

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 pressing F1 or by clicking the Help on This Function hyperlink.

The preceding example also demonstrates a new argument for the MacroOptions method. Excel 2010 accepts the ArgumentDescriptions argument. You can use this argument to provide a description of each argument in your function. These descriptions appear in the Function Arguments dialog box, which is displayed after the Insert Function dialog box.

A workbook that demonstrates this technique is available on the companion CD-ROM. The filename is function helpmyfuncs.xlsm.