How to apply a macro to all workbooks

[zigzagdna]

Whenever I open an excel workbook, or create a new work book, I want
cretain macros to be automatically applied. For example. I want
following workwook open be exceuted to all abobe work books (existing
and new...)"

Private Sub Workbook_Open()

MsgBox "Wecome, welcome..."

End Sub


Is there a way of doing it. I have setup this macro in personal.xlsb
file, but it does not get applied when I create a new workbook or open
an existing workbook. It seems to apply only to personal.xlsb file.


Thanks.

 

[Dave Peterson]

There are application events that excel monitors that you can hook into.

Saved from a previous post:

Put this in the personal.xls project's ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

'msgbox "Welcome..."

'this autofit columns if the opening workbook was text
Select Case LCase(Right(Wb.Name, 4))
Case Is = ".txt", ".prn", ".csv"
Wb.Worksheets(1).UsedRange.Columns.AutoFit
End Select
End Sub

Save your personal.xls.
Close excel and reopen it. Your personal.xls workbook show open and set up this
application event.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about those application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

 

[zigzagdna]

There are application events that excel monitors that you can hook into.

Saved from a previous post:

Put this in the personal.xls project's ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
     Set xlApp = Application
End Sub
Private Sub Workbook_Close()
     Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

     'msgbox "Welcome..."

     'this autofit columns if the opening workbook was text
     Select Case LCase(Right(Wb.Name, 4))
         Case Is = ".txt", ".prn", ".csv"
             Wb.Worksheets(1).UsedRange.Columns.AutoFit
     End Select
End Sub

Save your personal.xls.
Close excel and reopen it.  Your personal.xls workbook show open and set up this
application event.

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about those application events at Chip Pearson's site:http://www.cpearson.com/excel/AppEvent.aspx

Dave Peterson:

Thanks so much, this is what I was lloking for. I have a book on Excel
macros, but it does not have anything on application events.