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

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

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

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

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

Using a ComboBox control to select a help topic

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

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

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

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"

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

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