MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing the file name by 1 everytime i open the file

Posted by Gaz on September 06, 2001 1:07 AM


I would like to have a macro that generates a number everytime its open - ie remeber how many times its been openned and place it in a cell on a userfrom.

Can i do this?

Thanks in advance,

Posted by Ian on September 06, 2001 3:45 AM


Private Sub Workbook_Open()

Sheet1.Range("a1").Value = Sheet1.Range("a1").Value + 1

End Sub

Right click_view code double click the ThisWorkbook and paste it in.
change the a1 to the cell you want and sheet1 to the name of the sheet

any help?


Posted by Gaz on September 06, 2001 3:55 AM

Thanks - works a treat

Cheers Ian,

Works really well.


Posted by Henry Root on September 06, 2001 3:57 AM

You might also want to save the workbook so that the new number is preserved if a user closes without saving :-

Private Sub Workbook_Open()
Sheet1.[A1].Value = Sheet1.[A1].Value + 1
End Sub

Also, if you want to prevent users from changing the number :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Selection, [A1]) Is Nothing Then
Application.EnableEvents = False
On Error GoTo e
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = True
End Sub

Posted by Henry Root on September 06, 2001 4:08 AM

Also ....


For another way (quite a good one), have a look at Count_Seq_File at :-