Worked example

This example creates context-sensitive balloon Help for supporting the use of the SALESMAN workbook. In this example, the user will be presented with a balloon whose title is: 'Help Choices for using the Salesman system'.

The first thing to do is to define a v ariable that can store a Balloon obj ect. We can use a statement of the form:

Dim myBalloon As Balloon

You can create a Balloon object by using a property called NewBalloon. and this is assigned to the balloon variable. When assigning any object variable, we can use a statement of the form:

Set myBalloon = Assistant.NewBalloon

Listing 10.10 contains the VBA code that will implement the customised help menu as displayed in Figure 10.14. Notice that a variable of type Balloon has been declared with the line:

Dim BlnLabels As Balloon

E Microsoft Excel - SALESMAN

dsjxj

J Arial f*

J Arial f*

F

G

hi

1

j.

*

L

M

N

2.1

.ding the Sales To Date tc the Monthly Total

22'.

.¿Itiplying Monthly Total by the Bonus Rate

is

23

[l the: End of Month Sales column for the beginning i:if>-r^:'-t-i.-.n.

„„„Ii,

24

. of each new month

Help Choices for

Kelp-

25

using the Salesman

26

system

27

This will explain the

Sis

28

function of each option.

23

30

lies

31

4

Monthly Total

Monthly Bonus

End of Moi

© Bonuses

32

77

£

385 J10

© Sales to date

33

136

£

680 J30

© Monthly Bonuses

34

372

£

1,860.00

35

13

ISO

£

900 Jin

© Weekly sales

36

157

£

785 J10

64»

j

37-

78[ 193

£

96500

526

38

f 123

£

615.1)0

459

*

33

68 557

£

2,785J!D

1010

JU-.

40

it i it

£

610110

452

\

41

446

1917

9585

53491

• r

M <

* M / Sheet 1 \weeklysales / week sales test data / Summary / \ 1

Figure 10.14 Further explanation of selected options using MsgBox

Ready

Figure 10.14 Further explanation of selected options using MsgBox

Also, a variable has been created called IntReturnValue whose purpose is to store the return value of the user's selected choice, in order to generate the appropriate explanation that follows the original selection. This variable has been defined as:

Dim IntReturnValue As Variant

Next, the balloon variable BlnLabels is assigned to a balloon object with the statement:

Set BlnLabels = Assistant.NewBalloon

The statement Assistant.Visible = True will make the balloon visible. The statement With blnlabels will then be used to assign each of the label choices as shown below:

With blnlabels

.Heading = "Help Choices for using the Salesman system" .Text = "This will explain the function of each option." .Labels (1).Text = "Representatives" .Labels (2).Text = "Bonuses" .Labels (3).Text = "Sales to date" .Labels (4).Text = "Monthly Bonuses" .Labels (5).Text = "Weekly sales" .Button = msoBalloonTypeButtons .Show End With

The heading is assigned using the Heading property of the balloon object:

.Heading = "Help Choices for using the Salesman system"

An optional supporting text statement is then used using the Text property of the balloon object:

.Text = "This will explain the function of each option."

The label choices can each be set using the Labels property as follows:

.Labels (1).Text = "Representatives"

When the choices are complete, the Button property as in the statement...

.Button = msoButtonSetNone

.specifies which buttons should appear at the bottom of the balloon when all the text choices have been completed. In this example, we have set this property to None so that no button will be displayed at the bottom of the balloon. The default value for this property is an OK button. Some other possible values for Button properties are listed in Table 10.1. The method Show is then used to display the balloon using the statement:

.Show

The next section of the program assigns the value (in the range 1 to 5) of the choice selected by using the statement:

IntReturnValue = blnlabels.Show

The statement:

Select Case IntReturnValue

Will then use the Select Case construct (see Chapter 6) to display a more detailed explanation when a choice is selected from the balloon. The full program listing is shown in Listing 10.10.

Sub Assist() ActiveSheet.Unprotect Dim BlnLabels As Balloon Dim IntReturnValue As Variant Set BlnLabels = Assistant.NewBalloon Assistant.Visible = True With blnlabels

.Heading = "Help Choices for using the Salesman system" .Text = "This will explain the function of each option." .Labels (1).Text = "Representatives" .Labels (2).Text = "Bonus Rate" .Labels (3).Text = "Sales to date" .Labels (4).Text = "Monthly Bonuses" .Labels (5).Text = "Weekly sales" .Button = msoButtonSetNone .Show End With

IntReturnValue = blnlabels.Show Select Case IntReturnValue Case 1

MsgBox "The representatives column displays each rep name currently in the worksheet. This is the left-hand column and the rep names are not sorted in any particular order" Case 2

MsgBox "The Bonus rate is fixed for all representatives. The bonus rate can be read from cell B43" Case 3

MsgBox "The sales to date column displays the total sales to date for each representative during the current financial year." Case 4

MsgBox "The monthly bonus column displays the total monthly bonus accrued for each sales representative for the current month." Case 5

MsgBox "The range weekly sales contains the cell grid running from C32:F40 and displays each of the four weeks during the month." End Select ActiveSheet.Protect End Sub

Table10.1 Some common properties of the Balloon object

Property

Purpose and Illustration of use

Heading

Used to display the heading text that appears in the balloon. .Heading = "Help Choices for using the Salesman system"

Text

Used to display text after the heading but before any labels, or check boxes .Text = "This will explain the function of each option."

Labels (index)

Used to create a BalloonLabel object. Index is an integer between 1 and 5. i.e., 5 choices are possible.

.Labels (1).Text = "Representatives"

Button

Used to control the buttons at the bottom - possible values are: msoButtonSetOK - will display OK button only msoButtonSetNone - will display no buttons msoButtonSetYesNo - will display two buttons, labelled Yes and No msoButtonSetOKCancel - will display two buttons, OK and Cancel .Button = msoButtonSetNone

BalloonType

Used to format the labels that appear in the balloons. This property can be assigned to any of the following three values:

. msoBalloonTypeButtons - will display the labels in button format

.BalloonType = msoBalloonTypeButtons msoBalloonTypeBullets - will display the labels using bullet point format

.BalloonType = msoBalloonTypeBullets msoBalloonTypeNumbers - will display the labels in number point format .BalloonType = msoBalloonTypeNumbers

Table10.2 Some common properties of the Assistant object

Property

Purpose and Illustration of use

NewBalloon

Creates a customised balloon for the Assistant. For example, Set BalloonObj = Assistant.NewBalloon

On

This is a Boolean property. Setting the value to True turns the property on. Setting the value to False turns the Assistant feature off. For example, Assistant. On=False will remove the Assistant

Visible

This again is a Boolean valued property.

0 0

Post a comment