Solved hit counters in excel

December 19, 2012 at 23:47:07
Specs: Windows Vista
I would like to add hit counter in my work book so that every time when workbook has opened whether read only , the count increase

See More: hit counters in excel

December 20, 2012 at 01:14:39
✔ Best Answer
Interesting problem. Here is a stab at it.

To make this work ....
The macro needs to be in the ThisWorkbook module of the workbook you want to monitor
You need to create a workbook called Counter in the same directory as the workbook you are monitoring
Replace your directory references in the macro

Give it a try and see how it works

Private Sub Workbook_Open()

Application.ScreenUpdating = False

     Workbooks.Open ("C:\users\username\XL Files\Counter.xlsx")
        Workbooks("Counter.xlsx").Sheets("Sheet1").Range("A1") = Range("A1") + 1

End Sub

The reason the counter must be a separate file is that you want to count when the file is opened read only so that you can't store the counter in the file you are counting. You could probably send the counter to a text file as well but not sure of the script that could do that.

Report •

December 20, 2012 at 12:29:03
To add to AlteK's suggestion, I think this will work, but I don't have time to test it.

In the workbook that you are monitoring, set a cell that is linked to the cell in the Counter.xlsm file that stores the count. It should reflect the current value in that cell when you open the workbook that is being monitored. It won't save the value if the file was opened read only, but that shouldn't matter since it will be updated the next time the file is opened.

You might need to add an Application.Calculate instruction at the end of AlteK's code so that workbook calculates and reads the updated cell in the Counter workbook after it has been saved.

As I said, this is untested.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 13, 2013 at 21:17:33
thanks for your help altek this is doing well

Report •
Related Solutions

Ask Question