Setting up an automatic step
Click on the developer tab, then the insert icon. Choose the small box under the Active X controls, which inserts a Command Button. (It is important that you choose the Active X Command Button rather than the Form Control. In the second method, we will use the Form Controls.)
After you press the button, click on the spreadsheet. A new Command button “CommandBut” will appear wherever you click. This can be expanded to show the entire label “CommandButton21”. The last numbers might be different, especially when you add more buttons.
Make sure that the button is selected, and then click on “View Code”. The Microsoft Visual Basic for Applications will appear. In the pull down menu, select the CommandButton21, then enter the code shown in the diagram or listed below. (Note that Visual Basic uses ‘ to denote a comment. Also note that there are no characters like ; to denote the end of a command.)
Here is the code
Private Sub CommandButton21_Click() Dim PauseTime, Start Dim i As Integer i = 1 Do While i <= 100 ' Set number of steps. Sheet1.Cells.Range("A7").Value = i i = i + 1 PauseTime = 0.1 ' Set duration ' typically equal to interval. Start = Timer ' Set start time. Do While Timer < Start + PauseTime DoEvents ' Yield to other processes. Loop Loop End Sub
Make sure that the cell number in the
Sheet1.Cells.Range(“A7”).Value = i
line refers to the cell you have denoted as your step number. Once you are finished, exit out of the code editor.
Next, let’s make things look a little better.