creating a help guide within excel

[neodjandre]

has anyone ever created a help guide within excel for a big spreadsheet they developed?

can you suggest a way of doing this or an article to read?

could users press F1 and let my own custom help guide appear ?

thanks
andy

 

[hatman]

You can make Help Files as simple or complex as you want. At the simple end is to place the Help info on a Sheet that the user can view. if you already have Macros anyway, then you could place the Help info on a Userform, and display it in any one of several different ways. If the Help info is very involved, or the application is very complex, you may want to use a third party HelpFile creator that will allow you to make a wndows .hlp file, and offer Context Sensitive Help. This is very complex, and can take as long if not longer to develop than the original application.

Displaying help on F1:

Application.OnKey "{F1}", "Display_Help"

Where Display_Help is a Procedure that displays the Help Info (Either swap to the help sheet, show the Help Userform, or Open a separate Help File)

But remember to reset F1 to it's default behaviour when the application exit

Application.OnKey "{F1}"

Bueeln Bovey and Green have an EXCELLENT chapter on providing Help in their book

 

[neodjandre]

excellent, is there a free tutorial somewhere though? I will def buy the book sometime but I am in a hurry as we speak

 

[hatman]

Hmm, none of my usual resources seem to have anything useful. Exactly what level of help are you planning to implement? I have done all of the baove, plus several other variations. Perhaps I can give you enough of a start to get going.

 

[neodjandre]

something like a help menu with on index... on the left you have 10 categories (i.e. for the names of 10 sheets in my workbook) and when you click on them on the right pane, some guidance instructions appear..

like a standard software help menu i suppose...

 

[hatman]

Start by building the Help File. I like to use the Help Creator from Breit Technologies. It's free, it's simple, and I haven't found any bugs.

I highly recommend locating the Help file in the same directory as the workbook, and create a global constant called Help_File_Name to hold the actual filename

You can start the file at the index using the F1 key as I described above, use the help method:

Sub Display_Help()
    
    Application.Help ThisWorkbook.Path & "\" & Help_File_Name

End Sub

To jump to a Help Topic (in this case topic number 40), use the follwing method

Application.Help ThisWorkbook.Path & "\" & Help_File_Name, 40

To make a help topic available for a Msgbox:

MsgBox("This is taking too long... would you like to continue this manually?", vbYesNoCancel + vbExclamation + vbDefaultButton2 + vbMsgBoxHelpButton, "What Now?", ThisWorkbook.Path & "\" & Help_File_Name, 34)

Or an Inputbox

InputBox("Enter Drawing Number to Search.", "Drawing Search", , , , ThisWorkbook.Path & "\" & Help_File_Name, 44)

If you want to display help in conjuntion with Userforms, you need to set the Project Helpfile. In the Project Explorer, right click the Project, and got to the properties. Browse to the Helpfile in the proper firled. Now you can set the .HelpContextID for each object in each userform... just be sure to set the .WhatsThisButton and .WhatsThisHelp to true for the userform.

That should get you started... let me know if you have questions...

 

[neodjandre]

thanx hatmat, sorry for taking so long to reply.

is their a way to embed the .hlp file in the actual workbook so that there are no two separate files that need to be in the same directory ?

 

[hatman]

No. The .hlp file must be separate so that the appropriate .dll(s) can find it and manipulate it externally. That's what is actually happenning when you call the Help file using anyone of the methods I described: one or more external, common .dll's are being invoked to open the specified .hlp file, and manipulate it in a way so it looks like any othe rhelp file interface.

You can always package the various files in a self extracting zip file. There are also freeware installers out there that can be used to drill multiple files into a single file for a professional looking install.

 

[neodjandre]

great could you recommend a good installer ? even if it's not free I would be willing to pay some money for it !

 

[hatman]

It's been a long time since I used an installer utility. I have used the one that comes with VB 6.0 most frequently... before I got my hands on VB 6.0, I used a freebie... it's been YEARS since I've even had a freebie on on one of my systems... I'm afraid I don't remember what I used to use, though truthfully, there are probably better utilities available now. A quick google serach turned up the following:

http://www.thefreecountry.com/programming/setup.shtml

Lately I have been using some variation of the following method. It copies the addin file (and any associated help files, if needed) from a common network drive. This could be adjusted to use URLDownloadToFile to get one or more files from a website, if needed.

Sub Auto_Open()

    Dim mb As VbMsgBoxResult

    'If Environ("username") = "sasurpa" Then
    
        mb = MsgBox("Shall I quit?", vbYesNo)
        
        If mb = vbYes Then
    
            Exit Sub
        
        End If
        
    'End If
    
    
    Install_Wizard
    
End Sub

Function get_ANY_drive(ByVal lstrDrive_To_Get As String) As String
    
    Dim thisFileObj, allDrivesObj
    Dim item
    
    Dim lintSlash_Location As Integer
    
    Dim mb As VbMsgBoxResult
    
    Dim lboolFlag As Boolean
    Dim lstrRoot As String
    
    Set thisFileObj = CreateObject("Scripting.FileSystemObject")
    Set allDrivesObj = thisFileObj.Drives
    
    lboolFlag = False
    
    For Each item In allDrivesObj
    
        Select Case item.driveletter
            
            Case lstrDrive_To_Get
                lboolFlag = True
                lstrRoot = item.sharename
                lintSlash_Location = InStr(3, lstrRoot, "\")
                lstrRoot = Left(lstrRoot, lintSlash_Location - 1)
                get_ANY_drive = lstrRoot
                
        End Select
        
    '    Select Case item.DriveType
    '    Case 0: oneDriveType = " [ Unknown ] "
    '    Case 1: oneDriveType = " [ Removable ]"
    '    Case 2: oneDriveType = " [ Fixed ] "
    '    Case 3: oneDriveType = " [ Network ] "
    '    Case 4: oneDriveType = " [ CD-ROM ] "
    '    Case 5: oneDriveType = " [ RAM Disk ] "
    '    End Select
    
    
    Next item
    
    If lboolFlag = False Then
    
        mb = MsgBox("Please close Excel, map the " & lstrDrive_To_Get & ": drive, then restart Excel.", , lstrDrive_To_Get & ": Drive Not Mapped")
        
        get_ANY_drive = "False"
        
    End If
        
    Set thisFileObj = Nothing
    Set allDrivesObj = Nothing

End Function




Sub Install_Wizard()

    Dim nms() As String
    Dim vers() As String
    Dim mb As VbMsgBoxResult
    Dim root_V As String
    
    
    On Error Resume Next
    
    root_V = TurnBack_Distributor.get_ANY_drive("V")
    
    If root_V = "False" Then
    
        Exit Sub
        
    End If
    
    pth1 = Environ("APPDATA") & "\Microsoft\AddIns"
    pth2 = root_V & "\private\sasurpa"

    mb = MsgBox("I am about to install Paul Sasur's Turnback Wizard." & vbCrLf & "Proceed?", vbYesNo, "Install")
    
    If mb = vbNo Then
        
        Exit Sub
        
    Else
        
        AddIns.Add pth2 & "\TurnBacks.xla", True
        
        AddIns("Paul Sasur's TurnBack Wizard").Installed = True
            
    End If
    
    ThisWorkbook.Close
    
End Sub