Zooming and Scrolling a Sheet from a UserForm

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.

Figure 14-7: Here, ScrollBar controls allow zooming and scrolling of the worksheet.

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 .