In-line Help approach (2003 and earlier)

Topic

New Reply

I have tried searching for previous posts on this but found that “Help” is not a good word to use in a search.

In the attached file is the approach I am currently taking (message boxes launched by an image control) to displaying in-line help in a tool I am currently building. I have chosen this approach because I want to minimise external documentation and also to locate the help text where it is relevant. Up to now I have found this solution to be reasonable and well received by the tool’s users.

However, it is beginning to turn into an administrative burden (even with the use of global messages that are displayed by many of the buttons) due to the sheer number that I am having to build into the tool. I also do not like the way that Excel handles their location during cut and insert and also when rows/columns are deleted. The tool is designed for ongoing use by many and ideally without huge involvement from myself which means that very quickly there could be many KBs of the file taken up by obsolete buttons that cannot be seen and their associated code.

Other solutions I have at least partially considered are:
Data Validation input messages – I find the 255 character limit is too small for most messages
Cell Comments – I have a couple of resistances to this approach; users tend to ignore or overlook the flag, the comments cannot be guaranteed to appear alongside their marker, the boxes are affected by the freeze panes feature that is being used.
Custom text boxes and the Selection_ Change event – another very good possibility but also has similar limitaions to my current approach

I would appreciate any alternatives that you care to suggest.

Thanks,

Reply | Quote

Replies

You could store the texts to be displayed in a (hidden) worksheet in the same workbook or in another workbook, or in a text file (for example structured like an .ini file).

In the attached version of your workbook, I have used a hidden worksheet named Help (use Format | Sheet | Make Visible to see it). All buttons execute a line like this:

ShowHelp “SampleHelp”

ShowHelp is a macro in a standard module:

Sub ShowHelp(strName As String)
MsgBox Application.VLookup(strName, _
Worksheets(“Help”).Range(“A1:B1000”), _
2, False), vbInformation, “Help”
End Sub

The code would be different (and more elaborate) for a worksheet in another workbook or for a text file.

Reply | Quote

Hans,

Thanks for your response. It was along the lines of what I was thinking would be an improved solution. However, I thought it worthwhile to see if any cleaner approaches could be thought of.

I have incorporated this with a Selection_Change event and a test for a specific 4 character string at the start of the Target.Value with the text to lookup and display stored as the remainder of the string. This should:
1. allow me to limit the code required to a Selection_Change event on each worksheeet and the global ShowHelp macro
2. eliminate the possibility to leave orphaned buttons
3. make it much easier to add, delete and copy help text to cells
4. make it easier to create and edit help text
The only downside for me is losing the image which is very similar to Excel’s Help icon.

I am planning to incorporate this approach into the tool unless any further comments or suggestions are made before I get around to it.

Thanks again,

Reply | Quote

One method for help that I’ve used in my projects is a form that pops up (from a button or tie it into the F1 key). The form is made up of a list of topics which when clicked on, display the answer in a scrollable text field. I store the questions/topics and answers on a hidden sheet and use VBA to dynamically populate the form fields when the form is displayed. That way I don’t hard-code the questions into the form and it’s easier to maintain since it’s just two cols of text (one col for the question, 2nd col for the answer. It’s only limited to the # of characters in a cell but even if that’s not enough, you can use neighboring cells and write the code to check if a particular question’s answer takes up more than one cell and read accordingly (set a flag for each question to say how many cells are used to contain the answer, for example). So on your hidden help text sheet you could do this where col A is the # of cells consumed by the answer, col B is the question, and col C, D is the answer text:

1  QuestionA  AnswerA
1  QuestionB  AnswerB
2  QuestionC  AnswerC-1  AnswerC-2

In this case it shows that questions 1 and 2 have their answer in the 3rd column only, but the third question’s answer takes up two cells (since 1 cell wasn’t enough to hold all the text). Your code would read ‘2’ and know that it needs to read two cells to concatenate the answer when it’s displayed (using range.offset)

Another solution is to create regular Windows or html help. There are few kinds and I forget which is which as I’m writing this, but you can create these .chm files using any number of tools out there and invoke them like you see in most Windows programs. There are posts in the Lounge for these sort of help files. It also makes your program/tool more professional looking by having a built-in help system (more work to set up, but the point is to make it easier for the user).

Deb

Reply | Quote

Thanks for the suggestion.

I have finally included this functionality and have settled on a Selection_Change event identifying help prompts to find the desired help message from a library and display it in a message box. I wanted a solution which did not involve another file that had to always be associated with this file and I also have no desire to be the long-term administrator of this Tool so I wanted to make it as easy as possible to add and edit help text.

Thanks again,

Reply | Quote