The example in this section demonstrates how to use ScrollBar controls to allow sheet scrolling and zooming while a dialog box is displayed. Figure 14-7 shows how the example dialog box is set up. When the UserForm is displayed, the user can adjust the worksheet's zoom factor (from 10% to 400%) by using the ScrollBar at the top. The two ScrollBars in the bottom section of the dialog box allow the user to scroll the worksheet horizontally and vertically.
CD-ROM | This example, named zoom and scroll sheet.xlsm , is available on the companion CD-ROM. |
If you look at the code for this example, you'll see that it's remarkably simple. The controls are initialized in the UserForm_Initialize procedure, which follows :
Private Sub UserForm_Initialize() LabelZoom.Caption = ActiveWindow.Zoom & "%" ' Zoom With ScrollBarZoom .Min = 10 .Max = 400 .SmallChange = 1 .LargeChange = 10 .Value = ActiveWindow.Zoom End With ' Horizontally scrolling With ScrollBarColumns .Min = 1 .Max = ActiveSheet.UsedRange.Columns.Count .Value = ActiveWindow.ScrollColumn .LargeChange = 25 .SmallChange = 1 End With ' Vertically scrolling With ScrollBarRows .Min = 1 .Max = ActiveSheet.UsedRange.Rows.Count .Value = ActiveWindow.ScrollRow .LargeChange = 25 .SmallChange = 1 End With End Sub
This procedure sets various properties of the ScrollBar controls by using values based on the active window.
When the ScrollBarZoom control is used, the ScrollBarZoom_Change procedure (which follows) is executed. This procedure sets the ScrollBar control's Value to the ActiveWindow' s Zoom property value. It also changes a label to display the current zoom factor.
Private Sub ScrollBarZoom_Change() With ActiveWindow .Zoom = ScrollBarZoom.Value LabelZoom = .Zoom & "%" End With End Sub
Worksheet scrolling is accomplished by the two procedures that follow. These procedures set the ScrollRow or ScrollColumns property of the ActiveWindow object equal to the appropriate ScrollBar control value.
Private Sub ScrollBarColumns_Change() ActiveWindow.ScrollColumn = ScrollBarColumns.Value End Sub Private Sub ScrollBarRows_Change() ActiveWindow.ScrollRow = ScrollBarRows.Value End Sub
Tip | Rather than use the Change event in the preceding procedures, you can use the Scroll event. The difference is that the event is triggered when the ScrollBars are dragged - resulting in smooth zooming and scrolling. To use the Scroll event, just make the Change part of the procedure name Scroll . |