Excel irritation

M

Menno Hershberger

This has been bugging me for about three years now, so I thought I'd see if
there was a solution. I run a computer shop. I do all my invoices in Excel
and keep them all in one folder. Sometimes I want to go back and look at a
customer's invoice from the past to familiarize myself with what previous
repairs, upgrades, or whatever that I did before. I sort the folder by
date. Whenever I open the invoice to just look at it and go to close it
again it always asks me if I want to save my changes. I didn't MAKE any
changes! Once in a while, not thinking, I'll click Yes and then I wind up
with a new time stamp on the file, which of course puts it out of order.
The only way I can get it right again is to change my computer date back to
the original date and save it yet again.
Right now I'm running Office XP-SP2 but it did it in previous versions of
Office also.
Why does it do this and is there any way I can stop it?
Thanks.
 
B

Bigtalker

when you open the sheet, you have auto date set... therefore it asks you to
save changes because the date changed from when you last opened it...

so in fact, you did change something by simply opening the sheet...

ciao!
 
T

Trevor Shuttleworth

I suspect that you use =TODAY() or =NOW() in one of the cells in your
spreadsheet to automatically input the date when you produce the Invoice.

When you subsequently open the Invoice, Excel will update the date ... hence
asking you if you want to save changes.

I would suggest that before you "finalise" the Invoice, you copy the date
(or all the data) and Paste Special | Values to save the current value(s).
The Invoice will then never change. Problem solved ;-)

Regards

Trevor
 
M

Matt Lunn

You can bypass this by setting the workbooks Saved property to True during
the close event. Let me know if you need to know where to put the code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Sheets(1).Range("A1") = "Y" Then

ActiveWorkbook.Saved = True

End If

End Sub


You would need to have an indicator as to whether the invoice is complete or
not. In this example, if Cell A1 in Sheet 1 is not equal to Y then you will
get the save prompt. If Cell A1 contains the letter Y, the workbook will
close without saving.

HTH,

Matt
 
M

Mangus Pyke

This has been bugging me for about three years now, so I thought I'd see if
there was a solution. I run a computer shop. I do all my invoices in Excel
and keep them all in one folder. Sometimes I want to go back and look at a
customer's invoice from the past to familiarize myself with what previous
repairs, upgrades, or whatever that I did before. I sort the folder by
date. Whenever I open the invoice to just look at it and go to close it
again it always asks me if I want to save my changes. I didn't MAKE any
changes! Once in a while, not thinking, I'll click Yes and then I wind up
with a new time stamp on the file, which of course puts it out of order.
The only way I can get it right again is to change my computer date back to
the original date and save it yet again.
Right now I'm running Office XP-SP2 but it did it in previous versions of
Office also.
Why does it do this and is there any way I can stop it?
Thanks.
Click to expand...

Sounds like you have something in it that recalculates when you open
the document. You can set it to not recalculate:

Tools -- Options -- Calculation -- Manual

MP-
 
D

Debra Dalgleish

Another option, is to look at the file list in Date Created order --

In Excel, choose File>Open
Right-click on an empty part of the window, and choose View>Details
Right-click on the column headings, e.g. Name, Date Modified
Select Date Created
Click on the Date Created button, to sort that column
 
S

stefan via OfficeKB.com

Hi Menno,
Why dont you start saving your invoices with dates in the file names?
yyyy-mm-dd customer name.xls or so?
Stefan
 
M

Menno Hershberger

I thought about that. There's other files I do that way. Currently they
all start with the invoice number. I'm going to check in to what everyone
else has suggested here first.
Thanks for the suggestion.
 
M

Menno Hershberger

I suspect that you use =TODAY() or =NOW() in one of the cells in your
spreadsheet to automatically input the date when you produce the
Invoice.
Click to expand...

Nope... it doesn't do that. Actually this is just a slightly modified
copy of the "sample" invoice that came with one of the versions of Excel.
I'm trying to find it now and see if it did it in its original form.
When you subsequently open the Invoice, Excel will update the date ...
hence asking you if you want to save changes.

I would suggest that before you "finalise" the Invoice, you copy the
date (or all the data) and Paste Special | Values to save the current
value(s). The Invoice will then never change. Problem solved ;-)

Regards

Trevor
Click to expand...
 
Top