Counter for workbook

  • Thread starter Thread starter gilbert
  • Start date Start date
G

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
 
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
 
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
 
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
 
Back
Top