Clear dirty flag when workbook is opened

Carl Lindmark · 04-10-2005, 09:06 AM

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

Dave Peterson · 04-10-2005, 10:06 AM

You're going to have to use an application event.

Chip Pearson has lots of notes (and a sample workbook) at:
http://www.cpearson.com/excel/AppEvent.htm

Carl Lindmark wrote:
>
> 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

--

Dave Peterson

Carl Lindmark · 04-11-2005, 02:06 PM

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

"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i meddelandet
news:4259243D.D3811CB6@netscapeXSPAM.com...
> You're going to have to use an application event.
>
> Chip Pearson has lots of notes (and a sample workbook) at:
> http://www.cpearson.com/excel/AppEvent.htm
>
>
>
> Carl Lindmark wrote:
> >
> > 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
>
> --
>
> Dave Peterson

Dave Peterson · 04-11-2005, 02:06 PM

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

Carl Lindmark wrote:
>
> 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
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> news:4259243D.D3811CB6@netscapeXSPAM.com...
> > You're going to have to use an application event.
> >
> > Chip Pearson has lots of notes (and a sample workbook) at:
> > http://www.cpearson.com/excel/AppEvent.htm
> >
> >
> >
> > Carl Lindmark wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Carl Lindmark · 04-11-2005, 03:06 PM

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" <ec35720@netscapeXSPAM.com> skrev i meddelandet
news:425AB517.184C70ED@netscapeXSPAM.com...
> 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
>
>
>
> Carl Lindmark wrote:
> >
> > 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
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> > news:4259243D.D3811CB6@netscapeXSPAM.com...
> > > You're going to have to use an application event.
> > >
> > > Chip Pearson has lots of notes (and a sample workbook) at:
> > > http://www.cpearson.com/excel/AppEvent.htm
> > >
> > >
> > >
> > > Carl Lindmark wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

Carl Lindmark · 04-11-2005, 03:06 PM

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" <Replies_in_newsgroup_only@thanks> skrev i meddelandet
news:uS$acLsPFHA.3380@TK2MSFTNGP15.phx.gbl...
> 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" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> news:425AB517.184C70ED@netscapeXSPAM.com...
> > 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
> >
> >
> >
> > Carl Lindmark wrote:
> > >
> > > 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
> > >
> > > "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> > > news:4259243D.D3811CB6@netscapeXSPAM.com...
> > > > You're going to have to use an application event.
> > > >
> > > > Chip Pearson has lots of notes (and a sample workbook) at:
> > > > http://www.cpearson.com/excel/AppEvent.htm
> > > >
> > > >
> > > >
> > > > Carl Lindmark wrote:
> > > > >
> > > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
>