Controlling Sheets from UserForm

Controlling Sheet from a UserForm

Below is the example how to create and use ScrollBar controls to allow sheet scrolling and zooming while a dialog box is displayed. Below figure 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.

Follow these instructions to create a Zoom and Scroll Dialog Box for your project:

1.             Create your workbook.

2.             Activate the Visual Basic Editor (VBE) and insert a new UserForm into the project.The code in this example assumes that this form is named UserForm1.

3.             Place Scroll Bar controls as shown in below figure on UserForm1. 

Now Set the Properties for above controls.

Control Name	Property	Value
Frame1	Caption	Zoom
ScrollBar1	Name	ScrollBarZoom
Label1	Caption	%
Frame2	Caption	Scroll
Frame3	Caption
ScrollBar2	Name	ScrollBarRows
ScrollBar3	Name	ScrollBarColumns
CommandButton1	Name	CloseButton
CommandButton1	Caption	Close

Press Alt+F11 to activate the VBE. Make sure that the UserForm is displayed and double-click the CommandButton captioned Close.

Add below mention code in Module.

                                                Option Explicit

________________________________________________________________________________________________________________

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

________________________________________________________________________________________________________________

Private Sub ScrollBarZoom_Change()

    With ActiveWindow

        .Zoom = ScrollBarZoom.Value

        LabelZoom = .Zoom & "%"

        .ScrollColumn = ScrollBarColumns.Value

        .ScrollRow = ScrollBarRows.Value

    End With

End Sub

________________________________________________________________________________________________________________

Private Sub ScrollBarColumns_Change()

ActiveWindow.ScrollColumn = ScrollBarColumns.Value

End Sub

________________________________________________________________________________________________________________

Private Sub ScrollBarRows_Change()

ActiveWindow.ScrollRow = ScrollBarRows.Value

End Sub

________________________________________________________________________________________________________________

Private Sub CloseButton_Click()

    Unload Me

End Sub

Now Add a commandButton on sheet and write down the below mention mecro in general VBA module

Sub ShowForm()

    UserForm1.Show vbModeless

End Sub

Random Number Generator

The below example demonstrates how to animate a Label control. The UserForm shown in below Figure is an interactive Random Number Generator. Two TextBox controls hold the lower and upper values for the random number. A Label control initially displays Eight question marks, but the text is animated to show random numbers when the user clicks the Start button. The Start button changes to a Stop button, and clicking it again stops the animation and displays the random number. Figure below shows the dialog box displaying a random number between 1 and 1,000,000,000.

Please set the property of CommandButton as given below:

Name: StartStopButton

Caption: Start

The code that’s attached to the CommandButton is as follows:

Dim Stopped As Boolean

______________________________________________________________________________________________________________

Private Sub StartStopButton_Click()

    Dim Low As Double, Hi As Double

    If StartStopButton.Caption = "Start" Then

' validate low and hi values

        If Not IsNumeric(TextBox1.Text) Then

            MsgBox "Non-numeric starting value.", vbInformation

            With TextBox1

                .SelStart = 0

                .SelLength = Len(.Text)

                .SetFocus

            End With

            Exit Sub

        End If

        If Not IsNumeric(TextBox2.Text) Then

            MsgBox "Non-numeric ending value.", vbInformation

            With TextBox2

                .SelStart = 0

                .SelLength = Len(.Text)

                .SetFocus

            End With

            Exit Sub

        End If

' Make sure they aren’t in the wrong order

        Low = Application.Min(Val(TextBox1.Text), Val(TextBox2.Text))

        Hi = Application.Max(Val(TextBox1.Text), Val(TextBox2.Text))

' Adjust font size, if necessary

        Select Case Application.Max(Len(TextBox1.Text), Len(TextBox2.Text))

            Case Is < 5: Label1.Font.Size = 72

            Case 5: Label1.Font.Size = 60

            Case 6: Label1.Font.Size = 48

            Case Else: Label1.Font.Size = 36

        End Select

        StartStopButton.Caption = "Stop"

        Stopped = False

        Randomize

        Do Until Stopped

            Label1.Caption = Int((Hi - Low + 1) * Rnd + Low)

            DoEvents ' Causes the animation

        Loop

    Else

        Stopped = True

            StartStopButton.Caption = "Start"

    End If

End Sub