Microsoft Office Tutorials and References
In Depth Information
Option to Show Message Only Once
option to show Message only once
To see a dialog sheet in action, suppose you maintain an inventory of
clothing items and their retail prices, such as shown in Figure 25-3.
The prices are important and not meant to be changed without some
If you want to be made aware that a price was changed, you can
easily implement a Worksheet_Change procedure that informs you of a
change having been made in range B2:B10. You might eventually get
tired of being constantly reminded if you make changes often, so a
built-in utility for turning off the advisement would be nice.
In Figure 25-4, you see that the price of Jackets
was just changed from $65 to $57, and there is
your dialog box, dutifully telling you what you
already know. If you check the box next to Do
Not Show This Message Again, you won’t see
that dialog box again after making changes to
range B2:B10, for the rest of the time the
workbook is open.
The following examples of VBA code are what
make this possible without a UserForm. First,
because this is a Worksheet_Change event, place
this procedure in the worksheet’s module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“B2:B10”)) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
In a standard module, the following three macros comprise the VBA instructions. The primary
macro is named MsgBoxShowOnceOption , with two supporting macros for the Check Box evaluation
and the dialog sheet deletion.
‘Declare Public variables because several modules are involved.
Public dlgShowMessageOnce As DialogSheet
Public blnMessage As Boolean
Private Sub MsgBoxShowOnceOption()
‘This macro creates a fresh dialog sheet named “ShowOnce”,
‘to produce a dialog box that the user can set to stop being shown.
‘If the checkbox was checked to not show the message,
‘the Boolean variable blnMessage is False, so exit sub.
If blnMessage = False Then Exit Sub
‘Declare and define a variable name for the dialog.
Dim strDialogName As String
strDialogName = “ShowOnce”