Application Events / App_WorkbookOpen

[Fries]

Hello all,

What I´m trying to do:
Everytime I open an existing Workbook I want the PrintSettings for all
the sheets to be Draft. I cannot do this in the printersettings
directly because of restrictions.

What I came up with so far:
In my Personal.xls I did the following:

- I created a new class module: Class1
- I wrote in Class1:
Public WithEvents App As Application
- I wrote in Class1:
Private Sub App_WorkbookOpen(ByVal WB As Excel.Workbook)
Dim page As Worksheet
For Each page In Worksheets
page.PageSetup.Draft = True
Next page
End Sub

This doesn´t do the trick... can anyone tell me why not?

thanks
Fries

 

[Bob Phillips]

Fries,

You have to enable application events. I do this by adding this line in my
Workbook_Open event (for Personal.xls)


Set AppClass.App = Application

assuming the class is named AppClass. Have you done this?

That workbook event can also be run from the VBE by clicking in the event
code and run.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

You need to create an instance of Class1 in a normal code module,
and then set the App variable to the Application. E.g.,

Dim AppClass As Class1
Set AppClass.App = Application


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Dim AppClass As Class1

Set AppClass.App = Application

should be
Dim AppClass As Class1
Set AppClass = New Class1
Set AppClass.App = Application


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

missed the line
Dim AppClass as clsAppEvents

my class is called clsAppEvents

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Fries]

Hey Chip and Bob,

thanks for the help, got it working now.

Fries