How to apply a macro to all workbooks
 

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.

How to apply a macro to all workbooks
 

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


On 11/26/2011 20:49, zigzagdna wrote:
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

How to apply a macro to all workbooks
 

On Nov 27, 7:22*am, Dave Peterson wrote:
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

On 11/26/2011 20:49, zigzagdna wrote:





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- Hide quoted text -

- Show quoted text -

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.