Barrie Bariloche

Posted by Stephen Allenberg on January 18, 2002 5:23 PM

Barrie Bariloche

Sorry to confuse you Barrie, Ill just explain shortly Without getting involved in the details of XL and Visual Basic programming etc I am trying to create a worksheet that will always have its date of creation embedded in the file wherever this (Is this the Macro ???) is put. Now the formula below which was given to me by Barrie Davidson and I seemed to be able to get in OK (Is this the Template OR Macro ???) but from there I saved this as name.xlt. Now whenever I want to start or modify a job I pull this up for new job (or the old one) work on it save it as NEWname.xlt . My problem is .
Lets keep the info in cell Y1 & Z1 Modify as needed
what do I put into the cells Y1 & Z1 a formula Macro ??? date OR now() ot today () and why in 2 cells ???
I know I am most probably confusing a simple problem

Regards Stephen

Lets keep the info in cell Y1 & Z1 Modify as needed

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
if activesheet.name = range("Y1").value then
exit sub
else
range("Y1") = activesheet.name
range("Z1") = date
end if
End Sub

HTH

Posted by Bariloche on January 18, 2002 9:31 PM

Stephen,

The template file you created should appear in your list of "new" files (that is when you click File > New... you should see it listed along with "workbook" and any other templates that exist). When you select one of those templates a new file will be created. For example, lets say your template is called MyTemplate.xlt. When you click File > New... and select "MyTemplate.xlt" a new workbook will be created called "MyTemplate1.xls". Its created, but not saved. Assuming that this template had the macro in it, when you activate the sheet it will put the sheet name in cell Y1 and the date in Z1 unless the sheet name is already in Y1 in which case it won't do anything.

Cells Y1 and Z1 aren't special. You could change that code to be any cells you want. You could make them A1 and B1. In the original solution to your problem the cells Y1 and Z1 were just used as an example. Hence the "Modify as needed" phrase. If you don't want to put the info in Y1 and Z1 then just change it to whichever cells you want. Also, there's nothing magical about putting the sheet name in there.

Frankly, my solution would be to put "Last modified on: " and then the date in cell A1. And I wouldn't use the SheetActivate event, I'd use the SheetChange event (which I think is exactly what you originally requested).

Here's the code I would suggest:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Range("A1") = "Last modified on : " & Date
    Application.EnableEvents = True
End Sub

So, what to do now? You can do one of two things. If your template just consists of a standard workbook with the macro you were given before, then I'd suggest that you just create a new template with my code in it instead and save it over top of the old template (that is use the same name so that you destroy the old one by overwriting it). If your old template also had some additional formatting in it then it would probably be better to modify the old template. If you want to modify the old one then you need to Open the file as a .xlt file. In other words you click on File > Open (or if its listed in your recently used files you can click on it there). You don't want to do File > New... because that creates a new .xls file.

The code I posted above goes in the same place as the previous code you were given. That is, you have to open the VB Editor (alt+F11) and double click on the "ThisWorkbook" object in the Project Explorer window. If you modify your existing template be sure to delete the previous code. And then just paste in the code I provided above.

Give all that a try and see if it works out for you. If not, post again with whatever problem you're having and I'll see if I can help you out.

take care