Keeping track of clicks

[Rob]

Excel 2000

I have a sub "Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)" set up and want to record the number of times during the
worksheet is open that a user clicks the cell to trigger the above sub.

I've tried declaring Dim Utries As Long and then adding Utries = Utries +1
but this returns 1 each time the cell is clicked i.e. I think the contents
of Utries is cleared each time the cell is clicked.

I guess I've put code in the wrong place but am lost as to where it should
go.

Thanks for any ideas. Rob

 

[Chip Pearson]

Rob,

Declare the variable as Static and it will retain its value.
E.g.,

Static Utries As Long


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rob]

Chip,

Placing Static Utries As Long in the sub "Private Sub
Worksheet_FollowHyperlink(ByVal Target As Hyperlink)" and beneath this
Utries = Utries + 1 followed by debug.print Utries shows 1 each time the sub
is run. Should I declare the Static statement elsewhere?

Thanks, Rob

 

[Mike Fogleman]

Rob, Declaring a Static variable within the procedure is correct. This
worked for me in Excel 2000:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Static Utries As Long
Utries = Utries + 1
MsgBox "Hyperlink followed " & Utries & " times."
Application.WindowState = xlNormal
End Sub

The only thing, besides closing the workbook, that would reset Utries to 0
would be the End command or Utries = 0

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Static Utries As Long
Utries = Utries + 1
MsgBox "Hyperlink followed " & Utries & " times."
Application.WindowState = xlNormal
End
End Sub

or

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Static Utries As Long
Utries = Utries + 1
MsgBox "Hyperlink followed " & Utries & " times."
Application.WindowState = xlNormal
Utries = 0
End Sub

Mike

 

[Rob]

Mike,

End was the culprit, I've now changed the code to use Exit Sub which does
the job well.

Thanks, Rob