Excel BeforeSave 3

[simoncpage]

Hi all, how can I capture the beforesave method in excel on a global scale. I.e as you would in the Normal.dot document in word?

any help would be appreciated

Simon

 

[lovalles]

If you open the VB editor you can go on any file to ThisWorbook Section and there select the event you want


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

hope this helps
lovalles

 

[simoncpage]

that fine for each excel sheet opened but what I want to do is alter the global file for excel? like the normal.dot equivalent in word? do you see what I mean?

 

[RobBroekhuis]

One way of doing it is to hook into the File-Save and File-SaveAs menu commands and put your code in before returning control to the application. Others will have more detailed instructions...
Rob

 

[RobBroekhuis]

A little more detail around what I'm suggesting:

in personal.xls, put

sub workbook_open()
Dim co As CommandBarControl
With Application.CommandBars(1).Controls("File&quot
Set co = .Controls("Save&quot
co.Caption = "SaveOld"
co.Visible = False
Set co = .Controls.Add(before:=co.Index)
co.Caption = "Save"
cnAction = "MyNewSave"
End With
End Sub

Sub MyNewSave()
MsgBox ("you're trying to save?&quot
Application.CommandBars(1).Controls("File&quot.Controls("SaveOld&quot.Execute
End Sub

This creates a new menu item "Save", after renaming the old one, then calls the old one after performing your custom actions. You'd have to do the same for the SaveAs command, as well as the commandbars(2) controlbar (which is active when a chart is selected).
Rob

 

[rmikesmith]

lovalles,

An alternative is to set up an application-wide Workbook_BeforeSave event handler. This requires the use of a class module. Here are the steps:

1. Insert a Class Module into your project. Name it AppEventsClassModule (just to be consistent with my example code; you could name it anything you want). Insert the following into the Class Module:

Public WithEvents App As Application

Private Sub App_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
  'Your code here to do whatever before any workbook is saved
End Sub

2. Insert a standard Code Module (or use an existing one) and enter the following code:

Dim X As New AppEventsClassModule


Sub InitializeApp()
    Set X.App = Application
End Sub

3. In the ThisWorkbook Code Module, make the Workbook_Open procedure look like:

Private Sub Workbook_Open()
  InitializeApp
End Sub[code]


After this workbook opens, [b]any[/b] workbook saved will trigger the App_WorkbookBeforeSave event procedure.  I recommend converting the workbook to an add-in, so that it runs behind the scenes and can be installed/uninstalled as needed.

HTH
Mike

 

[rmikesmith]

Sorry, my reply was directed to Simon, not lovalles. It's late.


Mike

 

[RobBroekhuis]

Yeah, Mike, that's the approach I knew was possible but didn't remember how
Rob

 

[simoncpage]

Mike

How can I alter your code to apply to a workbook once it is opened? I.e workbooks open -> code from add-in run (as opposed to workbooks before save -> code from add-in run (as I want to run a code that requires the workbook to be saved first)

Thanks

Simon

 

[rmikesmith]

Simon,

Once you've created the class module, click the left dropdown in the editor window, select App, click the right dropdown and select WorkbookOpen. This will create the appropriate event handler shell. Add your code, as needed.


Regards,
Mike

 

[simoncpage]

thanks!