Clear dirty flag when workbook is opened

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

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

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

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

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

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
>
>

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

--

Dave Peterson

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" <ec35720@netscapeXSPAM.com> skrev i meddelandet
news:425AC5BC.8C6FF436@netscapeXSPAM.com...
> 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 Lindmark wrote:
> >
> > 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
> > >
> > >
>
> --
>
> Dave Peterson

It sure sounds like you got it working--which is a good thing!

Carl Lindmark wrote:
>
> 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" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> news:425AC5BC.8C6FF436@netscapeXSPAM.com...
> > 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 Lindmark wrote:
> > >
> > > 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
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

> It sure sounds like you got it working...
Yep, I sure did - thanks to you!

> ...--which is a good thing!
Yep, it sure is! :-)

Thanks again!
/Carl


"Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i meddelandet
news:425B289C.8E51FE7C@netscapeXSPAM.com...
> It sure sounds like you got it working--which is a good thing!
>
> Carl Lindmark wrote:
> >
> > 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" <ec35720@netscapeXSPAM.com> skrev i meddelandet
> > news:425AC5BC.8C6FF436@netscapeXSPAM.com...
> > > 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 Lindmark wrote:
> > > >
> > > > 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
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson