VBA Control Button

J. C. Daly
March 1, 2011

A button can be used to execute a Visual Basic subroutine.

In the Developer ribbon select insert
In the window that comes up as shown in Figure 1, click on the button icon.

Figure 1 Button ICON in the Form Controls window is shown.
Move the cursor to the point on the spreadsheet where you want the button to be and click.
In the Assign Macro window that comes up, select the macro to be controlled by the button. The countbyn macro can be seen. It was selected

Figure 2 Assign Macro window is show. countbyn was selected to be controlled by the button.
To change the button label; right click and select Edit Text

Now when you click on the button the countbyn subroutine is exectued.

Public Sub countbyn()

'    This subroutine counts by an increment, k and displays the
'    values on the spread sheet.

'    For example if the maximum value is 20 and the increment is 5,
'    5 10 15 20  will be displayed on the spreadsheet.

    Dim x As Integer
    Dim xmax As Integer
    Dim k As Integer
    Dim c As Integer
    Dim j As Integer
    Dim i As Integer

    With ActiveSheet
        xmax = .Range("xmax")      ' Read the maximum count from the cell "xmax"
        k = .Range("k")            ' Read the counting increment from the cell "k"
    End With

    c = 0       '    Initilize counters to zero
    j = 0

    For i = 1 To xmax                ' Loop from 1 to the maximum count
        j = j + 1                    ' Increment j counter
        If j >= k Then               ' Write to sheet every k count
            ActiveSheet.Cells(6 + c, 4) = i     ' Write to row 6 + c in column D
            j = 0                    ' Reinitilize the j counter
            c = c + 1                ' Increment c so the next write will be down one row.
        End If
   
    Next i

End Sub

Figure 3 The result of running the subroutine with a max count of 100 and an increment of 5 is shown. Note that the button label was edited to read RUN. When you click on RUN the count by 5 numbers appear in column D. Cell D2 has been renamed xmax and cell F2 has been renamed k.