CODEWITHSUNDEEP
vbaexcel
Jan Doggen
Jan Doggen

Reputation: 9096

Excel macro runs in personal.xls but not in any other workbook

I have placed the following macro in my c:\program files (x86)\microsoft office\office11\xlstart\personal.xls:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' http://stackoverflow.com/questions/15267796/macro-to-make-a-backup-while-saving-a-file
' http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx

  Dim BackupName As String

  MsgBox "ActiveWorkbook.Name: " & ActiveWorkbook.Name   'For debugging
  BackupName = ActiveWorkbook.Name
  BackupName = Mid(BackupName, 1, InStrRev(BackupName, ".")) & "bak"
'For testing    BackupName = "D:\temp\" & BackupName    'For testing
  BackupName = ActiveWorkbook.Path & "\" & BackupName

  ActiveWorkbook.SaveCopyAs BackupName
End Sub

From what I understand (e.g. second link mentioned above) this is supposed to run every time I save any worksheet, but this is not happening.
Only if I (edit and) save personal.xls do I see the MsgBox and the personal.bak file appearing.

What am I forgetting?

(Running W7 64-bits and Office 2003).

Upvotes: 0

Views: 2243

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I mentioned in the comments that what you have is a workbook-level event procedure. That is slightly different from a "macro" (your public macros would be available to any open workbook, but the worksheet or workbook-level events would not respond to other workbook events). What you need is to trap Application-level events.

I have not fully tested this but i did some simple test and it seems to work, (I am porting over some code that I use in PowerPoint to trap Application-level events). Give this a shot and let's see if it works for you.

First create an class object to handle the events.

In your Personal.XLS, add a Class module and name it cEventClass (you can name it wahtever you want, but it will be easier to follow my example if you use the same naming conventions that I'm using).

In this class module, put the following code:

Option Explicit
Public WithEvents XLEvent As Application


Private Sub XLEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

'This is just my test, you can place your own code (modified as needed) here:
MsgBox "BeforeSave " & Wb.Name

End Sub

We've now established the means to trap Application-level events, and we can see these available options in the dropdowns in the VBE:

enter image description here

Next, instantiate an instance of this class.

Probably the best place to do this will be during the Personal.XLS Workbook_Open procedure. You could put it in another sub and call it on-demand, etc., but if you put it in this event handler, I think it should work.

Add a normal code module (this can't go in an object or class module), do this:

Public cXLEvents As New cEventClass

Add some code to automatically instantiate the class

in the Workbook_Open procedure of your Personal.XLS, do this:

Option Explicit

Private Sub Workbook_Open()
    'Add this line in addition to any other code you might already have in this procedure
    Set cXLEvents.XLEvent = Application

End Sub

Now, sit back and let the handler do its job!

Now, when you go to save any workbook (not limited to the Personal.XLS) the Application event handler should pick it up, as verified with my example:

enter image description here

NOTE I think this will fail to work if you are debugging and "End" run-time; that will kill the cXLEvents and so the event handler will essentially be turned "off", so you would need to re-instantiate it.

Upvotes: 1

Related Questions