CODEWITHSUNDEEP
vbacounter
adt772
adt772

Reputation: 23

Creating a counter in VBA

I wanted to know if there is an effective way to create a counting mechanism on vba using a Worksheet_SelectionChange event? I want to count how many times a specific cell is selected. If the cell is selected then the variable will go up by one, otherwise no change is made to the variable.

Dim S As String
Dim count As Integer

count = 0
S = "$" & "D" & "$" & ActiveCell.Row

If ActiveCell.Address = S Then
    count = count + 1
Else
    count = count
End If

Upvotes: 2

Views: 40001

Answers (1)

RubberDuck
RubberDuck

Reputation: 12789

This should work for you. Place this code in the code behind module of the worksheet you want to track. Change the address string appropriately. Note that it is aboslute notation, so A1 doesn't work, but $A$1 does.

Option Explicit

Private count As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Debug.Print Target.Address
    If Target.Address = "$A$1" Then
        count = count + 1
        MsgBox count
    End If
End Sub

Also note that you have to declare count at the module level so that it doesn't go out of scope and get reset.

Upvotes: 2

Related Questions