Clear dirty flag when workbook is opened

  • Thread starter Thread starter Carl Lindmark
  • Start date Start date
C

Carl Lindmark

Hello everyone!

I have an Excel document with some worksheet functions that cause the dirty
flag to be set, so I've been looking for a way to clear the dirty flag as
soon as the document is opened (in order to avoid being asked to save the
document when it is only opened and closed, without having made any
changes).

Now, I know that it is possible to achieve this by putting code in a macro
for THAT PARTICULAR document, but since this triggers a question (about the
macro) to pop up every time the document is opened, and that is not an
option for me.

So, what I want to be able to do is put that very same code (that clears the
dirty flag when the document is opened) in the Personal.xls file and have it
work FOR ALL documents that are opened. I've been trying with code such as:

ThisWorkbook.Saved = True
Application.ThisWorkbook.Saved = True
Workbooks(1).Saved = True
ActiveWorkbook.Saved = True

Dim myworkbook As Object
Set myworkbook = Excel.Application.ActiveWorkbook
myworkbook.Saved = True

But none of these approaches have worked. It seems as though Excel can't
find/access the newly opened document from the code in the Personal.xls
file... Or at least, _I_ have not been able to achieve this. Could anyone
help me?

Thanks in advance!
/Carl
 
Thank you very much for the tip!

Unfortunately, I have not been able to make it work.

Here is the Class module I have put in Personal.xls:
--------------------------------------
Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Wb.Saved = True
End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub
--------------------------------------

And here is the code that I have tried to put both in the ThisWorkbook part
of Personal.xls and in the Excel document that I (most) want this script
for:
--------------------------------------
Option Explicit

Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub
--------------------------------------

What could be wrong?

Sincerely,
Carl
 
I didn't put your code in my personal.xl* file--I put it in its own workbook.

And it worked fine for me. Did you put this behind the ThisWorkbook module?

Option Explicit
Dim AppClass As New EventClass
Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub
 
I deleted the code from the Personal.xls file and tried putting it in its
own workbook, as you had done. Unfortunately, though, no change.

What I now have:
In the XLStart catalogue I created a special file to hold the code:
CLEAR_DIRTY_FLAG_ON_OPEN.xls

CLEAR_DIRTY_FLAG_ON_OPEN's "ThisWorkbook":
--------------------------------
Option Explicit
Dim AppClass As New EventClass
Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub
--------------------------------

CLEAR_DIRTY_FLAG_ON_OPEN's Class module:
--------------------------------
Option Explicit
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Wb.Saved = True
End Sub
Private Sub Class_Initialize()
Set App = Application
End Sub
--------------------------------

Thank you very much for trying to help me with this - I really appreciate
it!

/Carl
 
When trying to open an Excel document, Excel warns about a compilation error
(something that is not defined properly) and highlights the following row:
Dim AppClass As New EventClass

/Carl


Carl Lindmark said:
I deleted the code from the Personal.xls file and tried putting it in its
own workbook, as you had done. Unfortunately, though, no change.

What I now have:
In the XLStart catalogue I created a special file to hold the code:
CLEAR_DIRTY_FLAG_ON_OPEN.xls

CLEAR_DIRTY_FLAG_ON_OPEN's "ThisWorkbook":
--------------------------------
Option Explicit
Dim AppClass As New EventClass
Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub
--------------------------------

CLEAR_DIRTY_FLAG_ON_OPEN's Class module:
--------------------------------
Option Explicit
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Wb.Saved = True
End Sub
Private Sub Class_Initialize()
Set App = Application
End Sub
--------------------------------

Thank you very much for trying to help me with this - I really appreciate
it!

/Carl



Dave Peterson said:
I didn't put your code in my personal.xl* file--I put it in its own workbook.

And it worked fine for me. Did you put this behind the ThisWorkbook module?

Option Explicit
Dim AppClass As New EventClass
Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub
Click to expand...
not
Click to expand...
 
Ahhh.

I figured that you had renamed that class module to EventClass.

Back into the VBE.
select your project
expand all the branches
Select your Class (named Class1) module
hit F4
Change the name in that top box in the properties window.

Then run workbook_open once more.

(You didn't get the error in your previous version?)

Carl said:
When trying to open an Excel document, Excel warns about a compilation error
(something that is not defined properly) and highlights the following row:
Dim AppClass As New EventClass

/Carl
Click to expand...
 
Oops :-)
I'm sorry - I've never worked with application events before and for some
reason I had just figured that the name "EventClass" was a reserved word
used when one wants to add a new application event...

Now I realize what a stupid mistake I made there... *smiling, although
somewhat red-faced*

Thank you SO much for all your help!

Sincerely,
Carl



Dave Peterson said:
Ahhh.

I figured that you had renamed that class module to EventClass.

Back into the VBE.
select your project
expand all the branches
Select your Class (named Class1) module
hit F4
Change the name in that top box in the properties window.

Then run workbook_open once more.

(You didn't get the error in your previous version?)
Click to expand...
 
Back
Top