Counter

Hi Jeremyw,
If you want to improve your answer:
1) Take a look at the GetSetting and SaveSetting functions in VBA. They can be used to retrieve and store information in the user's registry. See http://j-walk.com/ss/excel/tips/tip60.htm for more details. This approach is good for templates (such as for invoice numbering).
2) You could also save the serial number to a text file on the server. The benefit of this approach is that several people can share copies of the workbook, each one getting a unique number.
3) To get around your problem with losing the updated value if you don't save the workbook, you could add a Save to your Workbook_Open sub.

I'd like to see what you can do with this question.
Cheers!

Brad

Ronny,  Thanks for the Question & Points!

Brad,

1) I get how this works, but the GetSetting & SaveSetting will only work if only one user is accessing the file from one workstation.  If the file was on the network, it would only count how many times I'm opening it, correct?  
  What exactly is the XYZ Corp and Budget?  The online help shows them

2)  You have any tips/links on saving the number to a text file?

3)  Duh!  Sometimes you don't think about the smallest things.  ;)

This is the one I came up with.  

Private Sub Workbook_Open()
    Dim Counter As Long, LastOpen As String, Msg As String

'   Get setting from registry
    Counter = GetSetting("XYZ Corp", "Budget", "Count", 0)
    LastOpen = GetSetting("XYZ Corp", "Budget", "Opened", "")
 
'   Display the information in a cell
    Range("A1").Value = Counter  
   
    'Uncomment these lines if you want a message box to popup when the workbook
    'is opened
   
    'Msg = "This file has been opened " & Counter & " times."
    'Msg = Msg & vbCrLf & "Last opened: " & LastOpen
    'MsgBox Msg, vbInformation, ThisWorkbook.Name
   
'   Update the information and store it
    Counter = Counter + 1
    LastOpen = Date & " " & Time
    SaveSetting "XYZ Corp", "Budget", "Count", Counter
    SaveSetting "XYZ Corp", "Budget", "Opened", LastOpen
   
'   Save workbook
    ActiveWorkbook.Save
End Sub

Thanks,

Jeremy

Disregard the Q about the XYZ Corp and Budget.....I figured those out.

Jeremy,
Reading and writing to the registry is a good solution for one user, but possibly multiple workbooks. I don't see it as suitable for use with multiple users on a network.

For saving the information to a text file (accessible on the network), take a look at the Input #, Print # and Write # statements in VBA. See http://j-walk.com/ss/excel/tips/tip95.htm

Brad