Help With Counting

[Box 666]

The following gives you a count each time a wksheet is accessed, which is
exactly what I want,.

What it Does At The Moment,

The following count is stored on the worksheet itself (say sheet1). It
then increases cell"A85" by 1 each time the wksheet is accessed

What I Would Like It To Do

Instead of having the counter on (sheet1) I would like to have the
counter on (sheet5) but still relating to the number of times sheet1 is
accessed of course.

If possible I would also like to keep track of the date&time the wksheet
is accessed. So on (sheet5) you would have a counter showing (say 4)
followed by the 4 dates/times it was accessed.. is this possible.?



Any assistance gratefully received... as you appreciate I am not a
programmer.

Bob



"The following count is stored in the worksheet itself. Right-click a
worksheet tab, then choose View Code from the Context menu. Excel displays
the Visual Basic Editor, where you should paste the following code:

Private Sub Worksheet_Activate()

Range("A85").Select

ActiveCell = ActiveCell + 1

Range("B85") = "times opened"

End Sub

 

[Harald Staff]

Hi Bob

Change code to

Private Sub Worksheet_Activate()
Sheets(5).Range("A85").Value = _
Sheets(5).Range("A85").Value + 1
End Sub

Addressing sheets is a design concern. Sheets(5) , as used above, will
address the fifth worksheet in the workbook no matter what name it has.
Sheet5 will address the original "Sheet5" no matter what it's called now or
where it is. Sheets("Sheet5") will address the sheet currently named Sheet5,
no matter where it is or whatever it was initially named. All ways will err
"if there is no such thing".

Now you're a programmer ;-)

HTH. Best wishes Harald

 

[Bob Phillips]

Hi Bob,

Try this

Private Sub Worksheet_Activate()
Dim cnt As Long
With Worksheets("Sheet5").Range("A85")
cnt = CLng(.Value) + 1
.Value = cnt
.Offset(0, 1).Value = "times opened"
.Offset(cnt, 0) = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)