The Scroll Bar Control

You have undoubtedly seen and used scroll bars in numerous applications for scrolling through lengthy documents or large figures. Scroll bars sometimes automatically appear on the sides and/or the bottom ofVBA controls so the user can view the entire content displayed in a control. Situations such as these require nothing extra from you, or your program—the scroll bars are simply there to provide the user with a method of seeing the complete content of the control; however, VBA also provides a Scroll Bar control that you may add to forms in your project to enhance an interface, such that the user may do more than just scroll through content.

There are several properties of the Scroll Bar control that are of interest to you as a VBA programmer (other than the usual appearance and size properties). Table 6.3 summarizes the major properties of the Scroll Bar control.

r

Table 6.3 Selected

Properties

H

of the Scroll Bar

Control

Name The name used for programmatic access to the control.

Min The minimum allowed value of the Scroll Bar. The minimum occurs when the scroll box is located at its minimum location.

Max The maximum allowed value of the scroll bar. The maximum occurs when the scroll box is located at its maximum location.

SmallChange Defines the amount the value of the Scroll Bar is incremented or decremented when the user clicks on either scroll arrow.

LargeChange Defines the amount the value of the Scroll Bar is incremented or decremented when the user clicks on the Scroll Bar on either side of the scroll box.

Value The value of the Scroll Bar as defined by range set by the Min and Max properties

Property Description

Name The name used for programmatic access to the control.

Min The minimum allowed value of the Scroll Bar. The minimum occurs when the scroll box is located at its minimum location.

Max The maximum allowed value of the scroll bar. The maximum occurs when the scroll box is located at its maximum location.

SmallChange Defines the amount the value of the Scroll Bar is incremented or decremented when the user clicks on either scroll arrow.

LargeChange Defines the amount the value of the Scroll Bar is incremented or decremented when the user clicks on the Scroll Bar on either side of the scroll box.

Value The value of the Scroll Bar as defined by range set by the Min and Max properties

You may use the Scroll Bar control to read or set the value for the property of another control or program object. Typically, the Scroll Bar control sets a value from a large range of choices. For example, you may use a Scroll Bar control on a form to provide the user with a method of activating a worksheet from all possible worksheets in a workbook.

The Change() and Scroll() events are the two most common event procedures associated with the Scroll Bar control. The Change() event procedure is triggered when the value of the Scroll Bar control is changed by the user. The Scroll() event procedure is triggered when the user drags the scroll box on the Scroll Bar control. The following code uses the Initialize() event of a UserForm object, and the Change() and Scroll() events of a Scroll Bar control (named scrWorksheet) to select distinct worksheets in the active workbook.

Private Sub scrWorksheet_Change()

Worksheets(scrWorksheet.Value).Select End Sub

Private Sub scrWorksheet_Scroll()

Worksheets(scrWorksheet.Value).Select End Sub

Private Sub UserForm_Initialize()

scrWorksheet.Max = Worksheets.Count scrWorksheet.LargeChange = Worksheets.Count / 5 End Sub

In this example, the Max and LargeChange properties of the Scroll Bar control cannot be set at Design Time because the number of worksheets in the active workbook is an unknown; therefore, these properties are set in the Initialized event of the UserForm object. The program uses both the Change() and Scroll() events of the Scroll Bar control to select a Worksheet object from the Worksheets collection object of the active Workbook object (not qualified in code). If the Scroll() event is not used, then the user will not see which worksheet is selected if he or she moves the scroll box by dragging. Instead, the user will not see the selected worksheet until the scroll box is released.

To test this code, open Excel and create a new workbook with multiple worksheets. Next, from the VBA IDE add a form and draw a Scroll Bar control onto it. Note that you can make a vertical or horizontal scroll bar by dragging the sizing handles of the Scroll Bar control horizontally or vertically on the form. Set the Name property of the Scroll Bar control and add the code to the form module's code window. Then, with the form selected, press F5 to run the program.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

Post a comment