Create a simple userform with a label and a Close button,as shown in Figure 20.12.Rename the form to HelpForm,the button to CloseHelp,and the label to HelpText. Size the label large enough to hold the help text. Add a macro behind the form to hide the form when the button is clicked:

Private Sub CloseHelp_Click() Unload Me End Sub

Figure 20.12

Create a userform with a label and button.

Insert a class module named clsLabel.You'll need a variable, Lbl,to capture the control events and a variable, ws,to capture on what worksheet the event occurs:

Public WithEvents Lbl As MSForms.Label Public WS As String

Also,you need a method of finding and displaying the corresponding help text:

Private Sub Lbl_Click() Dim Rng As Range

Set Rng = Lbl.TopLeftCell

HelpForm.Caption = "Label in cell " & Rng.Address(0, 0) HelpForm.HelpText.Caption = Rng.Offset(, 2).Value HelpForm.Show End If

End Sub

In the Thisworkbook module,create a workbook_0pen procedure to create a collection of the labels in the workbook:

Option Explicit

Option Base 1

Dim col As Collection

Sub Workbook_0pen() Dim wS As worksheet Dim cLbl As clsLabel Dim OleObj As OLEObject

0 0

Post a comment