Counting Woorkbook Openings

Put this in the Open Workbook event procedure. Put a cell address wehre you want the counter. I picked A1 below.

Private Sub Workbook_Open()
Dim num1 As Integer
num1 = 1
Range("A1").select
ActiveCell.Value = ActiveCell.Value + num1

End Sub

no need to select:




PS more robust versions of this would need to save the workbook after the change (just incase) & protect the cell in queston

Hi,

Further to Paddy's comments above, this version (by Aaron Blood) saves the count as a text file: See the useful links section for a link to AB's site XL-Logic.

HTH

Richie(UK) said:

Hi,

Further to Paddy's comments above, this version (by Aaron Blood) saves the count as a text file:

Code:

Private Sub Workbook_Open()
Dim x As Long

On Error GoTo ErrorHandler

One:
Open "c:\Counter.txt" For Input As #1
Input #1, x
Close #1
x = x + 1

Two:
Sheets(1).Range("A1").Value = x
Open "c:\Counter.txt" For Output As #1
Write #1, x
Close #1

Exit Sub
ErrorHandler:
    Select Case Err.Number
        Case 53  'If Counter file does not exist...
            x = InputBox("Enter Number to Begin Counting With", "Create 'Counter.txt' File")
            Resume Two
        Case Else
            Resume Next
    End Select
End Sub

See the useful links section for a link to AB's site XL-Logic.

HTH

Click to expand...

I have successfully managed to use this macro to count the number of times a file is opened whether the file is saved or not.

A number of questions regarding this please

1) Is it possible to change this macro to something that tells me how many times each username username has opened the file? To log this in a notepad file would be ideal.

2) i'm trying to input this macro into the middle of a macro i already have so it only counts the number of times the file has been open. This macro is at the bottom of this post & the count needs to apply to all users except those as follows: "David_g", "david_g", "john_ha", "alistair_k"

3) Also, curiosity is wondering how to get this macro to work without recording the numbers in another file? (i'm no expert & teh first macro on this page doesn't work when not saving files).


Here's that macro i was talking about:

Private Sub Workbook_Open()
Dim CurrentUser As String
CurrentUser = Environ("username")

Select Case CurrentUser
Case "David_g", "david_g", "john_ha", "alistair_k"
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Prodman").Visible = True
Sheets("Alternatives").Visible = True
Sheets("Compat Workout").Visible = True

Case Else

ActiveWindow.DisplayWorkbookTabs = False
Sheets("Prodman").Visible = xlVeryHidden
Sheets("Alternatives").Visible = xlVeryHidden
Sheets("Compat Workout").Visible = xlVeryHidden
MsgBox "Welcome to the Quote Sheet"

End Select

End Sub

Many thanks for any help advised.