How to Provide Help with Office Applications Written in Visual Basic for Applications
Context-Sensitive Help with WinHelp Files
UserForms in Microsoft Office applications use WinHelp HLP files by default. You can easily create WinHelp files using HelpScribble, and connect them to your Word, Excel and Access VBA applications. Note that different instructions apply for VB5 and VB6 as well as VB.NET applications.
First, you need to specify which help file to use. In the VBA Editor, select the name of your project, right-click, and select Properties. In the dialog that comes up, specify the file name of your help file.
To assign a help topic to a control on a UserForm, simply assign the Topic ID from HelpScribble to the HelpContextID property of the control in VBA. If the user presses F1 when the control has keyboard focus, the help topic will be displayed.
Show WinHelp Topics from Your VBA Code
You can show a WinHelp help topic from your VBA code by using the WinHelp API call directly. First, paste the following code into a separate module to get access to the API:
' Commands to pass WinHelp()
Public Const HELP_CONTEXT = 1 ' Display a given help topic
Public Const HELP_QUIT = 2 ' Terminate help
Public Const HELP_HELPONHELP = 4 ' Display help on using help
Public Const HELP_FINDER = 11 ' Show table of contents
Public Declare Function WinHelp Lib "user32" Alias "WinHelpA" _
(ByVal hWnd As LongPtr, _ ' Declare as Long on older 32-bit only versions of VBA
ByVal lpHelpFile As String, _
ByVal wCommand As Long, _
ByVal dwData As Long) _
Then you can display a particular topic by calling: WinHelp(0, strHelpFile, HELP_CONTEXT, iContextID) where strHelpFile is the full path to the .hlp help file, and iContextID is a Long containing the HelpScribble Topic ID of the topic you want to display.
To show the table of contents you created with HelpScribble's Contents Editor, call: WinHelp(0, strHelpFile, HELP_FINDER, 0)
Show HTML Help Topics from Your VBA Code
You can show a HTML Help (CHM) help topic from your VBA code by using the HtmlHelp API call directly. First, paste the following code into a separate module to get access to the API:
'Command to pass HTMLHelp()
Public Const HH_DISPLAY_TOPIC = &H0 ' Display the help file.
Public Const HH_DISPLAY_TOC = &H1 ' Display the table of contents.
Public Const HH_DISPLAY_INDEX = &H2 ' Display the index.
Public Const HH_DISPLAY_SEARCH = &H3 ' Display full text search.
Public Const HH_HELP_CONTEXT = &HF ' Display mapped numeric value in dwData.
Public Const HH_CLOSE_ALL = &H12 ' Close the help file.
Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" _
(ByVal hwndCaller As LongPtr, ByVal pszFile As String, _
ByVal uCommand As Long, ByVal dwData As Long) As Long
Then you can display a particular topic by calling: HtmlHelp(0, strHelpFile, HH_HELP_CONTEXT, iContextID) where strHelpFile is the full path to the .chm help file, and iContextID is a Long containing the HelpScribble Topic ID of the topic you want to display.
To show the table of contents you created with HelpScribble's Contents Editor, call: HtmlHelp(0, strHelpFile, HH_DISPLAY_TOC, 0). Make the same call with HH_DISPLAY_INDEX to display the keyword index you created with HelpScribble's Index Editor. Use HH_DISPLAY_SEARCH to display the full text search pane, if you enabled the full text search in HelpScribble's Project Options before compiling the help file.
Gustav Brock has published a more detailed article on controlling the HTML Help viewer from VBA.
HTML Help and Microsoft Access
If you follow the above instructions with Microsoft Access, you'll notice that while your help topic is displayed, the table of contents and index will be those of the Access help file rather than your help file. This is an issue with Access, and not with HelpScribble or your HTML Help file. Article Q271390 in Microsoft's knowledge base used to give some VBA code that could be used to work around this issue. Microsoft has removed this from their site. But you can still find the page in the internet archive.
HTML Help and Microsoft Excel
Excel 2007 may leave your .chm file open and even crash after closing your VBA application. The solution is to explicitly close the HTML Help file in the Workbook_Deactivate() event by calling HtmlHelp(0, strHelpFile, HH_CLOSE_ALL, 0). You can safely make this call even if the .chm file was already closed or never opened.
You Need HelpScribble to Provide Context-Sensitive Help with Your Applications