Counting the times a workbook has been opened

[haddonsman]

I'm trying to get a feel for how often some of our woorkbooks are used.

I can generate a message box that gives a count on open using this code;

Private Sub Workbook_Open()
notimes = GetSetting("MyCount", "A", "Count", 0) + 1
MsgBox "Workbook has been opened " & notimes & " times."
SaveSetting "MyCount", "A", "Count", notimes

... but I've no way of formatting that box ( I want to add some explanatory text over several lines)

I can create a userform in the format I want, and get it to display each time the workbook is opened, but I don't know how to incorporate the above code into it so I get to see the count.

Can I either amend the font/display values of the msg box or can I insert the count code into a userform?

Or could I do away with the boxes altogether and generate a count figure that only I have access to?

 

[HippoCampus]

Hi

You can have a message box pop up when it is opened by using this

Private Sub Workbook_Open()
notimes = GetSetting("MyCount", "A", "Count", 0) + 1
MsgBox "Workbook has been opened " & notimes & " times."
If Notimes > (5) then Application.Quit '<==change #
SaveSetting "MyCount", "A", "Count", notimes
End Sub

The number 5 should not be in brackets but this is the number of times I set to allow the workbook to open, place in here whatever number you want but once it gets past the stipulated amount it will automatically close the work book.

To reset to 0 run this code

Private Sub Workbook_Open()
notimes = GetSetting("MyCount", "A", "Count", 0) + 1
MsgBox "Workbook has been opened " & notimes & " times."
If Notimes > 5 then Application.Quit '<==change #
SaveSetting "MyCount", "A", "Count", notimes
DeleteSetting "MyCount", "A", "Count"
End Sub

Perhaps you could adapt this to your needs

Hippo

 

[haddonsman]

I 'm already generating a message box to do the calculation as stated but I can't change the font size/type, nor control the layout over several distinct lines

I want to be able to control the format, which is why I'm thinking of using a userform, but don't know how to insert that code into a userform.

 

[charlie1105]

Sorry, don't have time to go into much detail, but a couple of ideas for you to play with

1) you can create new lines in a message box using

"text line 1" & vbcrlf & "text line 2" etc

- the vbcrlf creates a new line - if you want an empty line, add 2 - ....& vbcrlf & vbcrlf & ....

2) you should be able to load a userform on workbook open - create a userform and use code like

userform.load
userform.show

You can then use the userform initialize to dynamically create the contents of a textbox or label

label1.value = "Workbook has been opened " & notimes & " times."

add as many labels / textboxes as you like

Hope this helps

 

[haddonsman]

The msg box code is spot on, thanks.

You can then use the userform initialize to dynamically create the contents of a textbox or label

label1.value = "Workbook has been opened " & notimes & " times."

That's the bit I have problems with; I can create a userform that displays on workbook open but I can't get that calcualtion to display - I just get the text in quotes, not the actual notimes value

 

[Kenneth Hobson]

I typically use the registry or a hidden worksheet to store audit data.

Similar to what Hippo said but with a MsgBox:

Private Sub Workbook_Open()
  Dim notimes As Long, myMsg As String
  
  notimes = CLng(GetSetting(ThisWorkbook.FullName, "Count", "Open", 0)) + 1
  SaveSetting ThisWorkbook.FullName, "Count", "Open", notimes
  
  myMsg = "This workbook has been opened " & notimes & " times." & vbCrLf & _
    "Todays date is: " & Format(Date, "mmmm, dd, yyyy") & vbCrLf & _
    "It is good to see you again " & Environ("username") & "."
  MsgBox myMsg, vbInformation, "Greetings"
End Sub

Private Sub reseetOpenCount()
  SaveSetting ThisWorkbook.FullName, "Count", "Open", 0
End Sub

 

[haddonsman]

OK - I can settle with using a msgbox now that I can add some blank lines into it.

Is it possible to track the number of times the sheet has also been opened read-only?

 

[Kenneth Hobson]

There is no need to settle.

  UserForm1.Load
  TextBox1.Value=myMsg
  UserForm1.Show

Here is a method to check for readonly.

Private Sub Workbook_Open()
  Dim notimes As Long, myMsg As String, wbName As String
  
  wbName = ThisWorkbook.FullName
  If (GetAttr(wbName) And vbReadOnly) <> 0 Then
    wbName = wbName & "_ReadOnly"
  End If
  
  notimes = CLng(GetSetting(wbName, "Count", "Open", 0)) + 1
  SaveSetting wbName, "Count", "Open", notimes
  
  myMsg = "This workbook has been opened " & notimes & " times." & vbCrLf & _
    "Todays date is: " & Format(Date, "mmmm, dd, yyyy") & vbCrLf & _
    "It is good to see you again " & Environ("username") & "."
  MsgBox myMsg, vbInformation, "Greetings"
End Sub

Private Sub reseetOpenCount()
  Dim wbName As String
  wbName = ThisWorkbook.FullName
  If (GetAttr(wbName) And vbReadOnly) <> 0 Then
    wbName = wbName & "_ReadOnly"
  End If
  SaveSetting wbName, "Count", "Open", 0
End Sub