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:[email protected]...
> 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" <[email protected]> skrev i meddelandet
> news:[email protected]...
> > 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" <[email protected]> skrev i meddelandet
> > > news:[email protected]...
> > > > 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
>
>