Counter for workbook

[gilbert]

Can we create a counter in a workbook that will count everytime th
workbook is open? I know the counter may not serve any purpose but i
the workbook is of personal use or for internal use, then it serve as
record of how many people has access to the workbook before.

any thouhgts here

 

[Ion Chalmers Freeman]

You could store a number in custom properties and increment it in
Workbook_oper

Option Explicit

Private Sub Workbook_Open()
Dim wkb As Excel.Workbook, dpsCount As Office.DocumentProperties,
dpCount As Office.DocumentProperty
Set wkb = Me
'get the list of custom properties
Set dpsCount = wkb.CustomDocumentProperties
'get the count of times it has been opened
On Error GoTo AddProp
Set dpCount = dpsCount.Item("penCount")
On Error GoTo 0
If dpCount Is Nothing Then

End If
dpCount.Value = Int(dpCount.Value) + 1
wkb.Save 'preserve this new count
Exit Sub
AddProp:
If Err.Number = 5 Then
Set dpCount = dpsCount.Add("OpenCount", False,
msoPropertyTypeNumber, 0)
Resume Next
End If
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
Err.HelpContext
End Sub

 

[GerryK]

Hi,
Could I please get some insight into this, it proves
useful for my work but I cannot seem to advance the number.
1. I placed this code into Modules.
2. When I save and open the file I go to File...
Properties and check Workbook_open (that I added in Custom
Properties) the number is not advancing from the 1 I
started with.

Is there something else to consider?

TIA

 

[Gord Dibben]

GerryK

The code goes into the ThisWorkbook module. Not into a general module and you
add nothing to Custom Properties.

With your workbook active, right-click on the Excel icon just left of "File"
on the menu bar.

Select "View Code".

Paste the Workbook_Open code in there.

Hit ALT + Q to return to Excel window.

Save and close your workbook. The counter will increment next time you open
the workbook.

Gord Dibben Excel MVP

 

[Ion Chalmers Freeman]

Sorry, that's a typo. It should say 'OpenCount'