excel problem
Posted by ClaytonCEI on June 11, 2001 12:01 PM
I created an excel template using excels' invoice template. I have made changes to it but I now need to find out how to keep todays date popping up in its cell everytime I bring up a new sheet or when I bring up a previous sheet. When I bring up a previous, older sheet, it erases the orginal date of the sheet and puts todays date on it. I can I fix this?
Posted by Ben O. on June 11, 2001 12:14 PM
There's VBA code that does that. Since the code is password-protected, it can't be changed to stop it from updating the date. What you can do is hold down the Shift key while opening the spreadsheet, which will stop the macros from running. You can also change your Excel security settings to prompt you before running macros.
Posted by CLaytonCEI on June 11, 2001 12:42 PM
thank you for replying, but what I really need to have accomplished is to keep the date field from changing to the current date, from the orginal date. I am currently running Excel 2000
Posted by Ben O. on June 11, 2001 1:49 PM
What I'm saying is that the VBA code in the invoice's Auto_Open macro changes the original date to the current date for some reason. The Date field does not contain a formula, so you know that it's being changed with a macro. The only thing you can do to prevent this is to stop the macro from running, and you can do that by holding Shift as you open the invoice.
Actually, there is one more way you can prevent the date from being changed. If you go to Insert > Names > Define and delete the name "data1", the date will not be changed the next time you open the invoice. That's because the VBA references the name "data1" in the procedure where it changes the date. If you delete the name, it can't change the date. The only problem is that the macro still tries to refence "data1", and if it's not there it'll give you an error message. You can get around this by adding the name "data1" to a cell that you don't use, and coloring it white so you can't see it. Here's some code you can run that will do just that:
Sub Invoicefixer()
ActiveWorkbook.Names("data1").Delete
ActiveWorkbook.Names.Add Name:="data1", RefersToR1C1:="=Invoice!R8C10"
Selection.Font.ColorIndex = 2
End Sub
It's an inelegant solution, but it works.
-Ben
thank you for replying, but what I really need to have accomplished is to keep the date field from changing to the current date, from the orginal date. I am currently running Excel 2000
Posted by Ben O. on June 11, 2001 1:51 PM
Whoops, there's an error in that code...
Here's the corrected code:
Sub Invoicefixer()
ActiveWorkbook.Names("data1").Delete
ActiveWorkbook.Names.Add Name:="data1", RefersToR1C1:="=Invoice!R8C10"
Range("data1").Font.ColorIndex = 2
End Sub
