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

• Unofficial Help system: This method of displaying help uses standard Excel components (such as a UserForm). Or you can simply display the support information in a text file, a Word document, or a PDF file.

• 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 different (and 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 book's website. Because most examples consist of multiple files, each example is in a separate directory.

Help Systems That Use Excel Components

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:

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

Figure 22-1: Using cell comments to display help.

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

A workbook that demonstrates the use of cell comments is available on the book's website in the cell commentsformletter.xlsm file.

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.

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 approximately 250 characters.

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

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

A workbook that demonstrates using a text box for help is available on the book's website in the textboxformletter.xlsm file.

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

Figure 22-3: An easy method is to put user help in a separate worksheet.

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.

This book's website 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 22-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.

Figure 22-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 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 book's website in the userform1formletter.xlsm file.

Using a scrolling Label to display help text

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

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.

A workbook that demonstrates this technique is available on the book's website in a file named userform2formletter.xlsm.

Using a ComboBox control to select a help topic

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

Figure 22-6: Using a drop-down list control to select a help topic.

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()

    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 book's website in the userform3formletter.xlsm file.

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 22-7 shows an example of help in a browser.

Figure 22-7: Displaying help in a web 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.

A workbook that demonstrates this technique is available on the book's website in the web browserformletter.xlsm file.

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.

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

Figure 22-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.

Figure 22-8: Displaying an MHTML file in a web browser.

A workbook that demonstrates this technique is available on the book's website in the mhtml_fileformletter.xlsm file. Also included is the workbook used to create the MHTML file (helpsource.xlsx). Apparently, some versions of Internet Explorer won't display an MHTML file that's hyperlinked from a Microsoft Office file if the filename or path includes space characters. The example on the book's website uses a Windows API function (ShellExecute) to display the MHTML file if the hyperlink fails.

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.

Using the HTML Help System

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. Each subsequent version of Office has used a different Help system. 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.

If you plan to develop a large-scale Help system, I strongly recommend that you purchase a help-authoring software product. 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 22-9 shows an example of a simple HTML help system.

Figure 22-9: An example of HTML Help.

A workbook that demonstrates this technique is available on the book's website in the html helpformletter.xlsm file.

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 22-10 shows HTML Help Workshop with the project file that created the Help system shown in Figure 22-9.

Figure 22-10: Using 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 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

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 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 deselect 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 VBE, press F2 to activate 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.

The Member Options dialog box is displayed, as shown in Figure 22-11.

Figure 22-11: Specify a context ID for a custom function.

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

A workbook that demonstrates this technique is available on the book's website in the function helpmyfuncs.xlsm file.