Tags: cause, clear, dirty, dirtyflag, document, everyonei, excel, flag, functions, ive, microsoft, msdn, software, workbook, worksheet

Clear dirty flag when workbook is opened

On Microsoft » Microsoft Excel

13,994 words with 9 Comments; publish: Wed, 04 Jun 2008 01:57:00 GMT; (306110.11, « »)

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

All Comments

Leave a comment...

  • 9 Comments
    • 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

      #1; Wed, 04 Jun 2008 01:58:00 GMT
    • 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      news:4259243D.D3811CB6.excel.todaysummary.com.netscapeXSPAM.com...[vbcol=seagreen]

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

      dirty[vbcol=seagreen]

      as[vbcol=seagreen]

      the[vbcol=seagreen]

      macro[vbcol=seagreen]

      the[vbcol=seagreen]

      the[vbcol=seagreen]

      have it[vbcol=seagreen]

      as:[vbcol=seagreen]

      can't[vbcol=seagreen]

      anyone

      > --

      > Dave Peterson

      #2; Wed, 04 Jun 2008 01:59:00 GMT
    • 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:[vbcol=seagreen]

      > 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      > news:4259243D.D3811CB6.excel.todaysummary.com.netscapeXSPAM.com...

      > dirty

      > as

      > the

      > macro

      > the

      > the

      > have it

      > as:

      > can't

      > anyone

      Dave Peterson

      #3; Wed, 04 Jun 2008 02:00:00 GMT
    • 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      news:425AB517.184C70ED.excel.todaysummary.com.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?[vbcol=seagreen]

      > Option Explicit

      > Dim AppClass As New EventClass

      > Private Sub Workbook_Open()

      > Set AppClass = New EventClass

      > Set AppClass.App = Application

      > End Sub

      >

      > Carl Lindmark wrote:

      part[vbcol=seagreen]

      the[vbcol=seagreen]

      flag[vbcol=seagreen]

      save[vbcol=seagreen]

      (about[vbcol=seagreen]

      an[vbcol=seagreen]

      clears[vbcol=seagreen]

      such[vbcol=seagreen]

      Personal.xls

      > --

      > Dave Peterson

      #4; Wed, 04 Jun 2008 02:01:00 GMT
    • 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.excel.todaysummary.com.thanks> skrev i meddelandet

      news:uS$acLsPFHA.3380.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]

      > 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      > news:425AB517.184C70ED.excel.todaysummary.com.netscapeXSPAM.com...

      > workbook.

      > module?

      > part

      script[vbcol=seagreen]

      > the

      > flag

      > save

      any[vbcol=seagreen]

      a[vbcol=seagreen]

      > (about

      not[vbcol=seagreen]

      > an

      > clears

      and[vbcol=seagreen]

      > such

      Excel[vbcol=seagreen]

      > Personal.xls

      Could

      >

      #5; Wed, 04 Jun 2008 02:02:00 GMT
    • 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:[vbcol=seagreen]

      > 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.excel.todaysummary.com.thanks> skrev i meddelandet

      > news:uS$acLsPFHA.3380.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > script

      > any

      > a

      > not

      > and

      > Excel

      > Could

      Dave Peterson

      #6; Wed, 04 Jun 2008 02:03:00 GMT
    • 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      news:425AC5BC.8C6FF436.excel.todaysummary.com.netscapeXSPAM.com...[vbcol=seagreen]

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

      error[vbcol=seagreen]

      row:[vbcol=seagreen]

      its[vbcol=seagreen]

      appreciate[vbcol=seagreen]

      ThisWorkbook[vbcol=seagreen]

      cause[vbcol=seagreen]

      dirty[vbcol=seagreen]

      to[vbcol=seagreen]

      made[vbcol=seagreen]

      code in[vbcol=seagreen]

      question[vbcol=seagreen]

      is[vbcol=seagreen]

      (that[vbcol=seagreen]

      file[vbcol=seagreen]

      code

      > --

      > Dave Peterson

      #7; Wed, 04 Jun 2008 02:04:00 GMT
    • It sure sounds like you got it working--which is a good thing!

      Carl Lindmark wrote:[vbcol=seagreen]

      > 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      > news:425AC5BC.8C6FF436.excel.todaysummary.com.netscapeXSPAM.com...

      > error

      > row:

      > its

      > appreciate

      > ThisWorkbook

      > cause

      > dirty

      > to

      > made

      > code in

      > question

      > is

      > (that

      > file

      > code

      Dave Peterson

      #8; Wed, 04 Jun 2008 02:05:00 GMT
    • > 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.excel.todaysummary.com.netscapeXSPAM.com> skrev i meddelandet

      news:425B289C.8E51FE7C.excel.todaysummary.com.netscapeXSPAM.com...[vbcol=seagreen]

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

      > Carl Lindmark wrote:

      some[vbcol=seagreen]

      compilation[vbcol=seagreen]

      following[vbcol=seagreen]

      meddelandet[vbcol=seagreen]

      in[vbcol=seagreen]

      code:[vbcol=seagreen]

      own[vbcol=seagreen]

      ThisWorkbook[vbcol=seagreen]

      this[vbcol=seagreen]

      meddelandet[vbcol=seagreen]

      that[vbcol=seagreen]

      the[vbcol=seagreen]

      asked[vbcol=seagreen]

      that[vbcol=seagreen]

      code[vbcol=seagreen]

      Personal.xls[vbcol=seagreen]

      with[vbcol=seagreen]

      though[vbcol=seagreen]

      this.

      > --

      > Dave Peterson

      #9; Wed, 04 Jun 2008 02:06:00 GMT