Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Counting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Counting
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 "Box 666" skrev i melding ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Counting
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) "Box 666" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
Counting | Excel Worksheet Functions | |||
Counting | Excel Worksheet Functions | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |