Counters using Macro-recorded key strokes and VBA Macro. Consider the following worksheet.

Question

a. Implement a counter in A3 using an assist value in B3 by recording a sequence of key strokes in a Macro “Count1” and assigned to the oval shape.

b. Compose VBA Macro “Count2” to implement a counter in D3 assigned to the rectangle.

c. Compose VBA Macro “Reset” assigned to the rounded rectangle shape that resets count values in A3 and D3 to zero.

Answer 1

a. Cell A3 contains the count. B3 contains formula =A3+1. Macro named “Count1” records following key stroke sequence: copy B3. Paste/special/values into A3. Esc (to remove dashed line). Select A3. Count1 Macro assign to oval.

The Macro in the VBA editor appears as the following.
Sub Count1()
‘ VBA from recording key strokes
Range("B3").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B14").Select
Application.CutCopyMode = False
Range("A3").Select
End Sub
b.
Sub Count2()
‘ VBA counter
Range("D3").Value = Range("D3").Value + 1
End Sub
c.
Sub Reset()
‘ VBA reset
Range("A3").Value = 0
Range("D3").Value = 0
End Sub