CB.CellPrefsND

Subtopics

This subroutine sets attributes of Crystal Ball cells without the Cell Preferences dialog. The default for any preference is the previous setting in the Cell Preferences dialog.

Caution!

CB.CellPrefsND cannot run if a cell with Crystal Ball data is located in a write-protected worksheet.

Table 19. CB.CellPrefsND Parameters

Parameter

VBA Data Type

Description

Index1

Integer

Changes assumption, forecast, or decision variable cells depending on the selected constant or index value:

  • cbCelAssum = 1

  • cbCelFore = 2

  • cbCelDecVar = 3

Index2

Integer

Sets preferences depending on the Value parameter that follows it. For constant and index values, see Table 20

Value

Variant

Works with Index2 to specify preference settings, as described in Table 21

DoNotApply (Optional)

Boolean

True applies changes only to new Crystal Ball cells. False applies changes to existing and new Crystal Ball cells. The default is False.

Level (Optional)

Integer

Specifies whether changes are applied globally to all open and new workbooks, to cells of that type in the active workbook, or to cells of that type in the active worksheet, depending on the selected constant or index value:

  • cbCelGlobal = 0

  • cbCelWorkbook = 1

  • cbCelWorksheet = 2

The Index2 parameter listed in the following table (Table 20) works with the Value parameter to set cell preferences. For Value parameter values, see Table 21.

Table 20. CB.CellPrefsND Index2 Parameter Values — Integer, Required

Constant Value

Index Value

Description

cbCelPattern

1

Sets a pattern choice defined by Value (Table 21)

cbCelColor

2

Sets a color choice defined by Value

cbCelNote

3

Turns notes on and off, depending on Value

cbCelDistMean

4

Sets the value in assumption cells to be the mean of the defined distribution

cbCelRangeMidpoint

4

Sets the value in decision variable cells to be the midpoint of the defined range

cbCelDistMedian

5

Sets the value in assumption cells to be the median of the defined distribution

cbCelRangeMin

6

Sets the value in decision variable cells to be the minimum of the defined range

cbCelRangeMax

7

Sets the value in decision variable cells to be the maximum of the defined range

The Value parameter values listed in the following table work with the Index2 parameter values to set cell preferences. For Index2 values, see Table 20.

Table 21. CB.CellPrefsND Value Parameter Values — Variant, Required

Used With Specified Values of Index2

Named Constant or Boolean Value

Index Value

Description

For Index2 = 1, cbCelPattern: Integer

n/a

0 through 17

0 specifies no pattern; 1 through 17 specify available patterns

For Index2 = 2, cbCelColor: Integer

n/a

0 to 15

0 specifies no color; 1-15 specify available colors

For Index2 = 3, cbCelNote: Boolean

True or False

n/a

True turns on notes; False turns off notes

For Index2 = 4, cbCelDistMean: Boolean

True or False

n/a

For assumption cells, True changes the cell value to the mean; False leaves the existing values in the cells

For Index2 = 4, cbCelRangeMidpoint: Boolean

True or False

n/a

For decision variable cells, True changes the cell value to the midpoint; False leaves the existing values in the cells

For Index2 = 5, cbCelDistMedian: Boolean

True or False

n/a

For assumption cells, True changes the cell value to the median; False leaves the existing values in the cells

For Index2 = 6, cbCelRangeMin: Boolean

True or False

n/a

For decision variable cells, True changes the cell value to the range minimum; False leaves the existing values in the cells

For Index2 = 7, cbCelRangeMax: Boolean

True or False

n/a

For decision variable cells, True changes the cell value to the range maximum; False leaves the existing values in the cells