Resetting data

Well,

I'm not a big fan of the way this is being done. First, its is writing and storing the number into your Windows Registry. You can see that in the code here:

The second issue here is that the code with start with 1, and count up 2, 3, etc separately for each user that opens it. so not sure if actually want to that to happen when sharing the document.

Personally, I would just have a hidden cell someplace and just update that cell value on each file open. And you hide that cell/sheet so others can't see it. make it "Very Hidden" from users.

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Workbook"
Const sKEY As String = "Open_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

'Saves Data In
'regedit, HKEY_Current_User, Software, VB & VBA Program Settings, Excel, Invoice, Invoive Key

'Auto Number'
With ThisWorkbook.Sheets("Sheet1")
            
    With .Range("B1")
        
            nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
            .NumberFormat = "@"
            .Value = Format(nNumber, "000")
            SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
        
    End With
End With
End Sub

Hmm, I don't like the method used.

At any rate, this edits Registry to enter Open_Key record and store it there.

To reset, in your Windows start menu, type in "regedit" and navigate to..

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Excel\Workbook

There you will find Open_Key Name. Edit the value for it.

I would do something like this. But note,the number is only updated if you save the workbook. So opening and closing will not update the value. Since the original code saves to registry, that code updates the value regardless if saved.

Private Sub Workbook_Open()
    
    Worksheets("Counter").Range("A1").Value = Worksheets("Counter").Range("A1").Value + 1
    Worksheets("Sheet1").Range("A1").Value = Worksheets("Counter").Range("A1").Value
     
End Sub

if you open this sample and the average user goes to 'unhide' the counter worksheet, they will not see it. You need to make it visible via the VBA Editor.

EDIT. I think you need this to start at 1 and count separate for each separate user? If so, the above will not do quite what you want.

Exact method will depend on workbook structure and what record you will keep, how often vouchers are issued by each user etc.

But, I'd likely keep a table in hidden sheet, where user name and voucher count is tracked and incremented.

For an example use Environ("UserName") to verify who's logged in and issuing voucher. And increment corresponding value.

Edit: I'd also add Event code on Workbook_BeforeClose to auto save workbook. Or add code to save when voucher is issued.

What's the best way to rid the registry of this tag for those of us who opened the file?

Originally Posted by AliGW

What's the best way to rid the registry of this tag for those of us who opened the file?

The Registry Key it created is called "Open_key" and is located at

HKEY_Current_User, Software, VB & VBA Program Settings, Excel, Workbook.

So on your window machine, press start and type regedit. Then navigate to that key and you can remove it. Note, this particular code and key addition is not harmful. I just don't like someone else's code modifying my registry.

Originally Posted by jammy1066

Right, well I am very much a VBA novice I'm sorry to say.

Would you be willing provide some code that could be used so that I could distribute this to other computers and users for them to use?

Jammy...... You need to provide us some more information. Do you want to the Invoice Number to start at 0 for all users? Or should the file just continue to count up each time anyone opens the file?

I provided a brief sample on the number counting up earlier and then CK_76 mentioned perhaps better to 'force' save the file on a workbook close. But we need to know what you are actually looking for.

Neither do I and it is going. Thanks for the pointer!

Don't worry, but it's an important lesson to all of us!

For the record, I don't believe that anybody thinks you are being difficult - in fact, you are doing just fine. You are learning and you are open to suggestion and help. All good - top marks!