Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply footer code to all new workbooks | Excel Programming | |||
Use VBA to Apply various Security Levels in Workbooks | Excel Programming | |||
Apply macro to closed workbooks | Excel Programming | |||
Apply macro to closed workbooks | Excel Programming | |||
Macro for Pivot Table - Apply to multiple workbooks | Excel Programming |