How mk code applicable to any active workbook?

C

Chet Shannon

I created a macro that will create footers for the active
wkbk, but does anyone know how to get a given macro to
run on any workbook that is open and not just the active
workbook? In other words I want this macro to be
executed on any workbooks that happen to be active at the
time. (Rather than be executable soley on one specific
macro.)

Thanks,
Chet
 
B

Bob Phillips

Hi Chet,

I think you are referring to application events. You can set it up so that
all workbooks print a value. To do this you need application events, and put
this is a separate workbook, such as Personal.xls, and then any workbook
will adopt these properties. No need to code each workbook with it.

This is what you need to do.

Firstly, all of this code goes in the the designated workbook.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet
.PageSetup.LeftFooter = "some text"
End With
End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

Chet Shannon

Thanks Bob... ! I will give it a try...
Chet
-----Original Message-----
Hi Chet,

I think you are referring to application events. You can set it up so that
all workbooks print a value. To do this you need application events, and put
this is a separate workbook, such as Personal.xls, and then any workbook
will adopt these properties. No need to code each workbook with it.

This is what you need to do.

Firstly, all of this code goes in the the designated workbook.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet
.PageSetup.LeftFooter = "some text"
End With
End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Top