Changing the file name by 1 everytime i open the file

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

Hi,

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,
Gaz

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

Try

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?

Ian

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

Thanks - works a treat

Cheers Ian,

Works really well.

Gaz

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
ThisWorkbook.Save
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.Undo
[A2].Select
Application.EnableEvents = True
Exit Sub
End If
e:
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 :-
http://geocities.com/aaronblood/pages/vba.html