[Excel VBA] How to Count File Opens | Auto-Tally with Macros

Introduction

Have you ever wanted to know how often a shared Excel file is actually viewed?

For example, it would be useful to know if “profile” or “guideline” files—which often exist just for the sake of formality—are actually being opened. Knowing the usage frequency can help in reviewing operations.

This article introduces a method to automatically and secretly count the number of times a workbook is opened using Excel Macros (VBA). We will focus on a method that hides the counter sheet so users do not notice it.

Objectives of This Article

• Automatically record the number of times an Excel file is opened.
• Keep the counter invisible to the user.
• Achieve this using VBA (Macros) rather than worksheet functions.
• Learn a method that requires minimal configuration.

Technology Used: VBA + VeryHidden Sheet

In this method, every time the workbook is opened, we increment a “count” stored in a hidden sheet. This sheet is set to “VeryHidden,” a state that cannot be unhidden through standard Excel operations, ensuring users rarely notice it.

Implementation Steps

1. Open the “ThisWorkbook” Module

1. Open the Excel file.
2. Press Alt + F11 to launch the VBE (Visual Basic Editor).
3. Double-click ThisWorkbook located inside “Microsoft Excel Objects” on the left side.

2. Paste the Code

Paste the following code into the window:

' ThisWorkbook Module
Option Explicit

Private Sub Workbook_Open()
    Const SHEET_NAME As String = "_Counter"  ' Name of the hidden sheet
    Const CELL_ADDR As String  = "A1"        ' Cell to store the count
    
    Dim ws As Worksheet
    Dim cnt As Long
    
    ' Prevent event loops (good practice when saving)
    Application.EnableEvents = False
    
    ' Attempt to set the worksheet object
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
    On Error GoTo 0
    
    ' If the sheet does not exist, create it and set to VeryHidden
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count))
        ws.Name = SHEET_NAME
        ws.Visible = xlSheetVeryHidden
    End If
    
    ' Read current value (treat as 0 if not numeric)
    If IsNumeric(ws.Range(CELL_ADDR).Value) Then
        cnt = CLng(ws.Range(CELL_ADDR).Value)
    Else
        cnt = 0
    End If
    
    ' Increment and write back
    cnt = cnt + 1
    ws.Range(CELL_ADDR).Value = cnt
    
    ' Save the workbook to persist the count
    ThisWorkbook.Save
    
    Application.EnableEvents = True
End Sub

How It Works

This code runs the moment the Excel file is opened.

1. Check Sheet: If a sheet named _Counter does not exist, it automatically creates one.
2. Hide Sheet: It sets the sheet to VeryHidden, so it cannot be unhidden via the normal right-click menu in Excel.
3. Increment: It reads the number recorded in cell A1, adds 1, and overwrites the cell.
4. Save: It executes ThisWorkbook.Save to ensure the new count is recorded.

How to Check the Count?

The _Counter sheet storing the data is in a “VeryHidden” state. To view it, follow these steps:

Steps to Unhide via VBE

1. Press Alt + F11 to open the VBE.
2. Select _Counter from the “Microsoft Excel Objects” or sheet list on the left.
3. Press F4 to open the Properties window.
4. Change the Visible property to -1 - xlSheetVisible.

The sheet will now appear in Excel, and you can check the value in cell A1.

Important Notes

• File Format: Save the file as an Excel Macro-Enabled Workbook (.xlsm).
• Security Warning: Users may be asked to enable macros the first time they open the file. Saving the file in a “Trusted Location” can suppress this notification.
• Protection: If you want to completely hide the count, apply a password protection to the VBA Project. This prevents other users from opening the VBE and seeing the hidden sheet or code.

Summary

This article introduced a method to implement an auto-incrementing access counter using Excel VBA. By utilizing a VeryHidden sheet, you can log access numbers without the user noticing.

Understanding “who, when, and how much” a file is used is highly effective for operational reviews and evidence gathering. Please try this method to visualize the actual usage of your shared files.