An Interactive Use of the Lanchester Combat Model

Technical Comments on Excel

3. Creating a Scroll Bar

The Scroll Bar dynamically updates bits of user-input information, thus making use of Excel’s interactive ability. In the Lanchester Combat Model, the parameter values are controlled by scroll bars, which are ‘linked’ to spreadsheet cells. Scroll bars may be a bit tricky initially, but the minor learning curve is worth the huge dividends of incorporating them in to a spreadsheet!

Step 1: Getting the Scroll Bar: Choose View, Toolbars & Control Toolbox.

 

Step 2: Choose the Scroll Bar. (Note: The Design Mode will turn on automatically!)

 

Step 3: Place the Scroll Bar on the worksheet. Begin with left-clicking the mouse and dragging an outline for the Scroll Bar. Release the left-click and the Scroll Bar will appear. The transition from one to the other is shown here. Note that only the Scroll Bar will remain, not the outline of it.

Step 4: Assign control over the Scroll Bar. Right-click the mouse and choose Properties on the following menu. The Properties menu appears, and this is where you gain control over the Scroll Bar. Note: While there may seem to be two Properties menus, they are the same, just displayed differently – either Alphabetic or Categorized (my preference!).

 

 

Step 5: The Properties Menu requires some attention – these instructions are critical! When done, close the Properties box, press the Esc key on the keyboard, and choose Exit Design Mode.

Item

Notes

Linked Cell

Note how the worksheet name is included with the linked cell reference. For example, if your worksheet is untitled and you were going to use cell A1, then the input is ‘Sheet1’!A1. As a force of habit, you may prefer to link to a ‘locked’ cell, for example, ‘Sheet1’!$A$1. These two Linked Cell entries are equivalent.

Max & Min

These entries define the domain of the specified parameter. The default maximum value is 32767; the default minimum is 0.

SmallChange & LargeChange

These entries define the incremental change in a parameter value with relation to moving the Scroll Bar and clicking on the Scroll Bar Arrows

Step 6: Click Away! As the Scroll Bar is dragged or clicked, the value of cell K40, which is mirrored in cell C2, changes!

Note: Should property changes ever need to be made to an existing Scroll Bar, do the following: View, Toolbars, Control Toolbox, choose the Design Mode, Left-Click on the Scroll Bar indicating the next action will apply to that object, Right-Click on the Scroll Bar to view the drop down menu of options for that object, and choose Properties.