We help IT Professionals succeed at work.

IsDirty

mksilk
mksilk asked
on
I have a file that has an Auto_Open macro attached to it that defines the page width etc for the workbook. The trouble is on closing, without performing any actions, Excel thinks the user has changed the file so it prompts for the user to save. I want Excel not to prompt for save unless the user does anything, ie disregard the Auto_Open macro as changes. I believe there is a ISDIRTY method (?) that can be used...
Comment
Watch Question

Commented:
This should do what you are after, on close it checks to see if any thing has be typed if it has it will prompt to save changes if nothing has been typed it will just close.

Private Sub Document_Close()
Dim varText
Selection.WholeStory
varText = Len(Selection)
If varText > 1 Then
ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
End If
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub



Cheers
Marcus

Author

Commented:
Comes up with an error saying Object does not support that property and highlights the Selection.wholestory line.

Commented:
Woops sorry I wrote it for Word....

Commented:
Try this


On Error GoTo err_test
    Cells.Select
    Selection.FindNext(After:=ActiveCell).Activate
err_test:
If Err.Number = 91 Then
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
ActiveWorkbook.Close SaveChanges:=True
Commented:
mksilk,

Simply add this line at the end of your Auto_Open macro:

  ActiveWorkbook.Saved = True

Ture Magnusson
Karlstad, Sweden

Author

Commented:
Soooooooo simple....but it works! Thanks for that. Should of thought of it myself, Doh!

Commented:
:o)
You're welcome, mksilk.

/Ture

Explore More ContentExplore courses, solutions, and other research materials related to this topic.