VBA & Macro Problems

How to Automatically Increment and Protect Invoice Numbers in Excel VBA

Huda Qurayshi Sep 30, 2026 868 views

Question details

The user wants to automatically increase an invoice number in cell B1 upon opening a workbook and securely protect it from manual modifications.

Automatically Increment and Protect an Invoice Number in Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an automated invoice template where the invoice number updates itself securely each time the file is opened.
Observed behavior: The user needs the worksheet to temporarily unprotect itself, update the numeric value in cell B1 by adding 1, and then re-protect itself automatically via VBA upon opening.

Before you start

Ensure your workbook is saved in a macro-enabled format (.xlsm) so the code runs properly when opened, and make a backup copy of your original template before adding VBA macros.

Solution 1Recommended

Automate Invoice Number Increment using Workbook_Open VBA

Place a VBA script in the ThisWorkbook module to unprotect the sheet, increment the value, and protect it again automatically upon opening.

This method ensures that every time the workbook is opened, the invoice number goes up by one, while keeping the cell locked so users cannot accidentally change it. It temporarily bypasses sheet protection using a predefined password.

Open the VBA Editor

Open your workbook in Excel and press 'Alt + F11' on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Access the ThisWorkbook Module

In the Project Explorer panel on the left side of the window, locate your project and double-click on 'ThisWorkbook'.

Insert the VBA Code

Copy and paste the following code into the code window: `Private Sub Workbook_Open() Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(1): With ws .Unprotect "YourPassword": If IsNumeric(.Range("B1").Value) Then .Range("B1").Value = .Range("B1").Value + 1: .Protect "YourPassword": End With End Sub`

Customize Password and References

Replace 'YourPassword' with your actual sheet protection password. Adjust 'Worksheets(1)' or 'Range("B1")' if your invoice number is located on a different sheet or cell.

Save as Macro-Enabled Workbook

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the code executes the next time it is opened.

OneDrive Sync Warning: Avoid using VBA to automatically rename files stored in OneDrive, as this can create synchronization conflicts. Manually rename the file if needed when saving your generated invoice.

Advanced Spreadsheet Automation

Automate Invoices Seamlessly with WPS Office

WPS Office provides robust support for VBA and macros, allowing you to easily run automated tasks like incrementing invoice numbers, managing templates, and protecting data without compatibility issues.

1. Open the Invoice Template in WPS: Launch WPS Spreadsheet and open your macro-enabled invoice template (.xlsm).
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Macro' or 'Visual Basic' to access the built-in VBA editor.
3. Implement the Macro Script: Paste your `Workbook_Open` script exactly as you would in Excel to automate the invoice number and sheet protection.
4. Save and Run: Save the document. WPS Spreadsheet perfectly preserves all macro logic and sheet protection settings, executing the automation each time the file is opened.

Fully compatible with Microsoft Excel VBA macros (.xlsm files)Lightweight and fast spreadsheet processingFree built-in developer tools for advanced automationFamiliar user interface for a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the invoice number update when I open the file?

Macros might be disabled in your security settings. When opening the workbook, ensure you click 'Enable Content' or 'Enable Macros' in the yellow security warning bar at the top of the spreadsheet.

Can I increment a different cell instead of B1?

Yes, simply change `.Range("B1")` in the VBA code to the specific cell reference where your invoice number is located, such as `.Range("F4")`.

What if my invoice number contains letters, like 'INV-100'?

The provided `IsNumeric` check will fail. You will need to modify the VBA code to extract the numeric part of the string using functions like `Right` or `Mid`, increment that number, and then concatenate it back with the 'INV-' prefix.

How do I stop the macro from running when I'm just editing the template?

Hold down the `Shift` key while opening the workbook. This bypasses the `Workbook_Open` event, allowing you to edit the template without incrementing the invoice number.

Excel & Data Problems