Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Workbook_BeforeSave

I am using the following routine on a template worksheet to perform a few checks prior to the file saving Code:
The code checks if certains cell are blank and if not relates an error message to the user.

As this is a template file, data will be entered onto it and then saved with a different name (the code only relates a message to the user if they are attempting to save the amended file as the template file), but when I try to use 'Save As' it fires off this routine and doesn't let me save the file.

I there a way of soing the above but if 'Save As' is used it doesn't fire off?

Thanks


Post your answer or comment

comments powered by Disqus
Hi

I have code that runs on the workbook BeforeSave event.
How do I "cancel" the actual save process here? Depending
on what the code returns, I want the save process
cancelled and the workbook left on the screen.

Thanks

Cindy

In a Workbook BeforeSave procedure, I've got a Message box to which I
expect a Yes/No response.

If the answer is No then I want to simply quit the procedure without
the workbook saving. It seems that whether the answer is Yes or No,
the procedure continues to the end and saves the workbook.

I've tried putting an Exit Sub, or End command after the test but to
no avail.

Can anyone help me out please.

Usual TIA

Rgds,

Richard Buttrey

Hi all,

I have a spreadsheet that I'm working on and I'd like to include a beforesave macro to format the spreadsheet.

Basically I need a macro to select all rows with data in column 'a' and add a thick border to the top of the cells. Ideally the border would stop at column 'i' but that's not essential.

I've attached an example of what I'd like. I've included 2 sheets 1 called 'before' and 1 called 'after'. They show what I'm after.

Thank you so much for your help.

Andrew

Hello:
I would appreciate help from you experts out there on this one!

I have a read-only excel file which users use as an template to enter
information & then store the files in a directory after giving them easy to
identify names of individual persons.

Cell A1 contains the first & last name of the person (eg John Doe)
Cell A2 contains an unique identifier number (eg 12345)

In order to have uniformity of file names (rather than users giving them
names on their own), I am using the following code in a macro button (called
Save) placed on the sheet to generate a file name based on contents of cells
A1 & A2

ThisWorkbook.SaveAs Filename:="c:My documentsSpecial Folder" &
Range("a1") & "- No " & Range("A2") & ".xls"

This works well & gives the blank template a filename such as "John
Doe -12345.xls" & saves it in the appropriate directory. However once the
file is saved with this name & the user clicks on the Macro button (Save)
next time, it displays the Excel message, which alerts the user that the
file already exists & asks whether you want to replace it with choices of ,
"yes", "no" or "cancel". Selecting "no" or "canel" results in an Run time
error. What additional code do I need to prevent this?

Also perhaps I need to modify the code so that the macro runs only if the
user is using the blank template for the first time. For all subsequent
time, clicking on the macro should only save the file & not do a saveas
features.

I am a novice at this, & am sure that there is a better/more elegant way of
achieving what I am trying to do. Should this code be in a module or should
I put it in the Workbook BeforeSave/ BeforeClose part?

Will sincerely appreciate your expert help.

TIA
Maria

I am trying to unprotect a spreadsheet from some code in a workbook. Below
is the code:

ActiveWorkbook.Sheets("TRANSACTIONS")
. Unprotect strPassWord
‘*** do stuff to worksheet
End with

This code is in a xla workbook.

If the code is called from the activeworkbook in a non save mode then it
works.

If the code is called from the Workbook.BeforeSave event then the code stops
with a protection error.
At this point, even in a debug mode, the sheet protection will not turn off.

Is there a known issue that prevents the activeworkbook sheet protection to
not be change during the before save event?

Any thoughts or work around?

Thanks in advance

--
Stewart Rogers
DataSort Software, L.C.

The code below (found online and then altered) has allowed me to prompt the user if any cells are left blank, and it works well. However, the user can disable the macros and get around having to complete the needed data.

I've been trying to find a code that will work with this so I can have the workbook open to a cover page (hiding all the other tabs) asking the user to open with the macros enabled.

Because I'm unable to freely design VB code, I've been finding volunteered codes online and trying to alter them to work. So far I haven't been able to actually hide the tabs (3 of them) if opened with macros off.

Can someone please help me write a code or send me a sample I can play with to be able to do this? We use version 2003 currently.

Code is in "ThisWorkbook." Option Explicit is in "General/Declarations" and the rest is in "Workbook/BeforeSave."

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myRanges As Variant
Dim iCtr As Long

myRanges =
Array(Me.Worksheets("Request").Range("d19,e19,d20,d21,d32,e32,d33,d34,h11,i11,j11,k11,g12,l12,m12,n12,k9,g11,m9"))

For iCtr = LBound(myRanges) To UBound(myRanges)
With myRanges(iCtr)
If .Cells.Count <> Application.CountA(.Cells) Then
MsgBox "Please fill in all these and other similar blank cells: " _
& .Parent.Name & vbLf & .Address(0, 0)
Cancel = True
Exit For
End If
End With
Next iCtr
End Sub
Thank you!

I have VB code written in an Excel 2003 spreadsheet that requires a user to enter data in a specific cell before the workbook can be saved. This same "beforesave" function also has a time delay (IF...THEN) written so the users can save the file to their C drives before a certain date. After that date, they must have something in that cell before they can save the workbook. There is also a message that pops up when they try to save the workbook without entering data in the cell. This parts works fine!

I now want to have two cells require data entry before a user can save the workbook but I don't know where to write the conditions for that second cell and I would also need a second message.

The second cell is H12 and the message would be similiar to "Current Installed System Info Required"

Code:
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Date < #11/2/2008# Then Exit Sub
If IsEmpty(Sheets("Request for PTM").Range("H11").Value) Then
Sheets("Request for PTM").Activate
Range("H11").Select
MsgBox "Estimated Order Date Required"
Cancel = True
  End If
End Sub
Can someone show how that second cell requirement would integrate with the above?
Thanks.

Hello all!

I have an issue with a command bar whose buttons shall work only when a certain workbook is active; this also when it has changed name due to an 'Save as...' operation. For that to work, a global variable needs to be updated every time when the workbook changes name.

The problem arises when the user saves the workbook under a new filename, which he is intended to be allowed (Save as...). With the BeforeSave event however I cannot catch the *new* filename into my comparison variable, but only the old one. I would need an event like 'AfterSave'.

Anyone with any idea on how to work this?

If a BeforeSave sub in Book2.xls contains a statement to
save Book1.xls, both workbooks are saved when you save
Book2.xls

If Book2.xls is saved by a BeforeClose sub (for
Book2.xls), however, then Book1.xls is not saved.

'code in Book1.xls ThisWorkbook code pane
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
End Sub

'code in Book2.xls ThisWorkbook code pane
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeClose"
ThisWorkbook.Saved = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
Workbooks("Book1.xls").Save
End Sub

My workaround for the problem is to save Book1.xls in
both the BeforeClose and BeforeSave subs. But the code
posted ought to work.

If it matters, I'm using Excel 2003 (11.6113.5703).

..again, thanks to this forum...great people....

I have my VB code working great - spreadsheet now requires data entry in a specific cell and that requirement has been delayed until a certain date with a IF THEN statement. Thanks to all that helped!!

I am now trying to broaden the "beforesave" requirement to multiple cells in the same workbook/worksheet. In the below VB statement, I don't know where to insert this additional command to require that a second cell have data entered into it before saving. The cell on the "Request for PTM" worksheet is H12.

I would need a second message to appear relating to that specific cell as well; similiar to first message "Current Installed Irr. System Entry Required"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Date < #11/2/2008# Then Exit Sub
If IsEmpty(Sheets("Request for PTM").Range("H11").Value) Then
Sheets("Request for PTM").Activate
Range("H11").Select
MsgBox "Estimated Order Date Required"
Cancel = True
  End If
End Sub
Thanks for all the great help!! You guys are awesome!!!

I am trying to find out more info regarding the BeforeSave workbook event, but I am having no luck. So can anybody help me out by giving me an example of a short procedure that would prevent the user from saving the workbook with the current workbook name, and would automatically force the file to save as a web page? I'm sorry that I don't have any examples of code that I'm working with, and it feels pretty juvenile to just ask someone to write code for me. But I haven't been able to get past step one with this.

Thanks a bunch,
Jim Stewart

In the various codes I am using AutoOpen, AutoClose, Workbook_BeforeClose and Workbook_BeforeSave Subs. BeforeClose and BeforeSave are in '_ThisWorkbook'; AutoOpen and AutoClose are in a Module. I also use Workbook_Open, Workbook_Activate and Workbook_Deactivate, also in _ThisWorkbook. I put message boxes thru out code to be sure code was running right. The Workbook_BeforeClose is running twice under certain condition (when a cell in active workbook sheet is blank ("")). I do not understand why it is running twice. Any ideas?

For condition where there is data in cell M10, this creates a whole other issue (multiple running of mutliple various Subs) which I believe I should deal with in different thread/question.

Thank you for any help.


	VB:
	
) 
     
    Worksheets("PanelSched").Activate 
    MsgBox "Test-Before close" 
    If Range("M10")  "" Then 
        Dim PanelName As String 
        PanelName = Range("M10").Text 
        If TypeName(PanelName) = "Boolean" Then Exit Sub 
        Application.DisplayAlerts = False 
        ActiveWorkbook.SaveAs FileName:= _ 
        PanelName & ".xls" _ 
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 
        Application.DisplayAlerts = True 
        MsgBox "Test-PanelName  "" " 
        Run "ExportData2" 
    Else 
        MsgBox "Must Enter Panel Name Before Saving" 
        ThisWorkbook.Close Saved = True 
        Exit Sub 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

In the situation where I shut down my model (i.e. not just press Ctrl+S, but shut it down), the following code saves the model twice. I only want it to save once. I really can't see why it saves a second time. Could anybody please review and help?

NB: I know if you set Cancel to True in the BeforeSave event, it should prevent the model from saving after the event, but even when set to true in the below code, it still saves...


	VB:
	
) 
    bIsClosing = True 'Given we shut model, this public bIsClosing is set to true...
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
) 
    Dim wsArray() As Variant 
    Dim iCnt As Integer 
    Dim wsSht As Worksheet 
    Application.ScreenUpdating = 0 
     
    Splash.Visible = True 'Splash is codename for warning spreadsheet, this is left
     'visible while all other sheets are to be hidden...
     
    For Each wsSht In ThisWorkbook.Worksheets 'Run through and hide each worksheet that
         'is not Splash
        If Not wsSht.CodeName = "Splash" Then 
            If wsSht.Visible = True Then 
                iCnt = iCnt + 1: Redim Preserve wsArray(1 To iCnt) 
                wsArray(iCnt) = wsSht.Name 
            End If 
            wsSht.Visible = xlSheetVeryHidden 
        End If 
    Next 
     
    Application.EnableEvents = 0 
    ThisWorkbook.Save 'Workbook saves here as I want it to
    Application.EnableEvents = 1 
     
    If Not bIsClosing Then 'Given bIsClosing is set to true, this IF section (to
         're show all sheets) is bypassed
        For iCnt = 1 To UBound(wsArray) 
            Worksheets(wsArray(iCnt)).Visible = True 
        Next iCnt 
        Splash.Visible = False 
    End If 
     
    Application.ScreenUpdating = 1 
     
    Cancel = True 'and so it shouldn't save, but it still does.
     'In fact it doesn't just save, it asks the user if they want to save the model, as
     'if there have been changes made to it since it was last saved, which there
     'hasn't been because the model was saved during this code further above.
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Do I need to make sure events are disabled at the end of the code? I would have thought not as that would leave events disabled full stop.

Any help really appreciated. Thanks,

Cnsleepy

Hi. I try to save another workbook, say Book_B, inside a BeforeSave routine for say Workbook A. It works fine when triggered by the CommandBar for saving A, but skips the "Save" when triggered by saving A from another workbook, say Book_1. Is this my fault, or some inherent limitation in Excel, and if so: is there some easy workaround? Any ideas? Thanks a lot.

Following is some simplified code to illustrate the situation:

This is the code for Book_A:


	VB:
	
) 
    Dim fName As String 
    fName = "Book_C" 
    For Each w In Workbooks 
        If w.Name = "Book_B.xls" Then w.SaveAs Filename:=fName 
    Next w 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is the code for Book_1:


	VB:
	
 Book_A_saving() 
    For Each w In Workbooks 
        If w.Name = "Book_A.xls" Then w.Save 
    Next w 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I tried making the title as useful as I could. I did do a search and found a similar problem, however, the poster marked it solved without posting his solution.
Here is the link to that post:
http://www.ozgrid.com/forum/showthre...ght=BeforeSave

What I am trying to do is open a workbook, print some preselected sheets and then close and save the workbook. This macro itself runs fine, as does the BeforeSave macro I want to use. However, when I am closing the workbook from the print macro, the BeforeSave does not work. It will either not run at all, or look as though it is running, but not actually perform the tasks (both of these have happened).

I can attempt to post any sample code you all may want, but the BeforeSave code is combersome (I inherited it) and the print code is... complicated. It is my first attempt. So, I will leave it to you to ask for what you want to see rather than try to figure out what you will want out of the mass of it.

Thank you in advance for any help.

I've used the following code with the Worksheet_Change change event and it worked great, but I would now like to use the same code under the BeforeSave event. For some reason it errors out on the first line. Any suggestions? TIA

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

'Enter today's date in Column H if value of cell in Column C changes

If Target.Column = 3 And Target.Row  1 And Target.Row  2 Then

'Enter date
    Range("H" & Target.Row).Value = Date
    
'Set cell color to green
    Range("H" & Target.Row).Interior.ColorIndex = 4
    Range("H" & Target.Row).Interior.Pattern = xlSolid
    Range("H" & Target.Row).Interior.PatternColorIndex = xlAutomatic
          
End If

Application.ScreenUpdating = False

'Open PartsListCostingWORKING to update links
Workbooks.Open ("G:SchedulingPartsListCostingWORKING.xls"), UpdateLinks:=True

'Close workbook and save
ActiveWorkbook.Close (True)

Application.ScreenUpdating = True

End Sub


Hi

I am trying to force the user to save an excel workbook with a particular name.

In the BeforeSaveEvent I am using the GetSaveAsFilename method and putting the name I want into the dialog box.

However when I then do thisworkbook.saveas the BeforeSave Event runs again. Any ideas of how to stop this.

Thanks

If iChangeFilename = vbYes Then

Cancel = True

sGetName = Application.GetSaveAsFilename(sFormattedFilename)

ThisWorkbook.SaveAs sGetName

Else

MsgBox "Filename has NOT been changed"

End If

Hello,

I have a file which is uses the following code:

The "BeforeSave" event is defined, and opens another file (Test2.xls), uses some data from it to make some updates, and then closes it.

I also use the BeforeClose event to ask if the user wants to backup their file to a pre-determined backup directory. (I don't want to ask that question everytime they save, only when they're done for the day). Of course, I want to save the file in the current directory, and then save it to the backup directory so that the backup and the "real" file are identical.

Here is my code:

------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'For clarity, I have deleted the code where I ask if they want to backup.
' If "Yes" then...
ActiveWorkbook.Save
'Here is where the code to backup the file goes...
'End if
End Sub
-----------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Set wb = Workbooks.Open("Test2.xls")
wb.Close
Set wb = Nothing
End Sub

------------------------------------------

The BeforeSave event works fine when the file is saved. Test2.xls opens and closes, no problem. But when the BeforeSave event runs as a result of the Activeworkbook.Save command in the BeforeClose event, the "Test2.xls" does not open, and the wb.Close statement causes an error.

I can't figure out why the Test2.xls file does not open in that case.
Any ideas?

Thanks.

Fuzz

Everytime I try to do a saveas for this particular workbook the **** thing jumps to one of my combo filter change procedures first and tries to run it and it causes an error. I've tried to disable events with the beforesave procedure but it doesn't seem to work. Has anyone ever had this problem before!? I could really use some input on this issue. THANKS!!!

I've got this code in the BeforeSave event of my workbook:

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

If MsgBox("Have you researched and manually entered the correct" _
& vbNewLine & "amounts for Proceeds from" _
& " Sale of PPE?", vbYesNo, "Reminder") = vbYes Then
ActiveWorkbbook.Save
Else
Sheets("Cash Flow").Select Range("B25").Select

End If
End Sub

When I get my MsgBox and click YES, I get a run time error and the code
stops on the ActiveWorkbook.Save line.
If I click NO, the workbook saves, despite the fact I only want to go to a
specific cell. How do I fix this code so when I click TES the workbook
saves, and when I click NO it goes to the specified cell?

My main macro is in an .xlA file; it operates on a 'host' .xlS file an
pulls data from several other .xlS files into three worksheets in on
file, processes that data, then depending on the value of a specifie
cell in each worksheet, sends up to three e-mails (I am referring t
this process as 'First E-Mail'); each e-mail sent has a correspondin
worksheet attached as an .xlS file. All of this works fine.

I am utilizing an .xlT file that is stored on a shared drive. Thi
file contains, in the 'ThisWorkbook' module, 'BeforeSave Events' cod
that saves an .xlS file (worksheets are first copied to the .xlT fil
by the main macro), after it has been modified by an user, to
specified location and also sends an e-mail with such modified file a
an attachment (I am referring to this process as 'Second E-Mail').

So, I have created code that will copy each of the three worksheet
created by my main macro (depending on the value of a specified cell i
each worksheet, there will be 0, 1, 2, or 3 workbooks created). Eac
copy will be to a corresponding empty worksheet in the .xlT file tha
contains 'BeforeSave Events' code in the 'ThisWorkbook' module.

This is a snippet, in a 'For i = 1 To 3' loop, from my main macro:

Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("R:PAS IncomeFUND ACCT RETUR
E-MAIL.xlT")

wb1.Worksheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)

'DELETE EMPTY WORKSHEET
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

wb2.SaveAs "R:PAS IncomePAS AND-OR ACCT ADJUSTMENTS" & _
"ACCT ADJUST - " & ActiveSheet.Name & " " & Format(myDate
"mm-dd-yy") & ".xlS"

If i = 1 Then fName1 = ActiveWorkbook.Name
If i = 2 Then fName2 = ActiveWorkbook.Name
If i = 3 Then fName3 = ActiveWorkbook.Name

ActiveWorkbook.Close False

Below is my code in the 'ThisWorkbook' module of my .xlT file:

Option Explicit
Dim myPath2 As String
Dim myDate As Variant

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

'IF PATH EXISTS, CODE IS PROCESSED AS A RETURN E-MAIL (SAVE
MODIFIED .XLS FILE & E-MAILS SUCH FILE AS ATTACHMENT)
'IF PATH DOES NOT EXIST, THE MAIN MACRO IS CREATING .XL
ATTACHMENT
If ThisWorkbook.Path "" Then
myPath2 = "R:PAS IncomePAS AND-OR ACCT ADJUSTMENTS"
myDate = Date - 1

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myPath2 & "COMMENTS - "
ActiveSheet.Name & " " & Format(myDate, "mm-dd-yy") & ".xlS"
Application.DisplayAlerts = True

'THIS CALLS PROCEDURE FOR SENDING SECOND E-MAIL
Call Second_Email

Application.Quit
End

Else
'DO NOTHING
End If

End Sub

A little rationale for this: the main macro sends the First-E-mai
with the appropriate workbook as an attachment; the user receives an
opens the e-mail; opens the .xlS file; keys in his/her explanation fo
receipt of such e-mail; clicks on save; and the user is done. That'
when the code in 'BeforeSave Events' kicks in, saves the modified .xl
file in the specified location, and sends the Second E-Mail with suc
modified .xlS file as an attachment.

PROBLEMS:
- In my .xlT file, if I delete the 'ThisWorkbook.SaveAs ...' stmt
everything works just as I intend. However, with such stmt, Exce
crashes -- even though Excel crashes, the file is saved in th
appropiate location, but no e-mail is sent. Help.
- Variable 'myDate' is defined in my main macro (it is 1, 2, 3, or
calendar days prior to Date; this date is next preceding business day)
I want to include this variable in the 'ThisWorkbook.SaveAs Filenam
...' stmt in my .xlT file; I do not want this variable to change onc
it is brought into the .xlT file. How can I do this? One potentia
solution would be to replace the text 'ACCT ADJUST' with the tex
'COMMENTS' in the names of the respective files created in the mai
macro and in the .xlT file. How can I do this? (this would eliminat
the need to pass 'myDate' from the .xlA file to the .xlT file; also
since a procedure in my main macro is named 'Auto_Open', if I include
Reference to the .xlA file in the .xlT file, the macro goes berserk).

Jingle123

--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=465970

Hi,

I've just had to do a messy workaround using a public boolean because the BeforeSave event isn't running properly when I save from the vba (even though it is firing).

I've attached a workbook that demonstrates the issue - basically I am making some sheets hidden before saves (so the user must enable macros to see them). In the example, 'Sheet2' should be hidden before saves but doesn't do so when using a VBA save method.

Thanks for any advice,

Chinchin

I have build an Add-in and capture the Beforesave Event of all Excel
workbooks my users work with. occasionally my users will open excel workbooks
from a database were the files are actually embeded objects. I have to treat
these files differently than the files my user manipulates on the Hard drive.
Currently I differentiate them by getting the OpenPath property from the
Workbook. If it is an Object OpenPath="" all other files have OpenPath =
"<path of some sort>" which I then use for further validation. This works
great as long as the file I'm working with is not a new workbook that has
never been saved, in that instance OpenPath="" which makes my code think it's
opend from the database as an object while it is not.
Anybody can think of an easy way in the beforesave event to differentiate
the Object from a new workbook ? This is probably realy simple I can just not
think of it right now.

My main macro is in an .xlA file; it operates on a 'host' .xlS file and pulls data from several other .xlS files into three worksheets in one file, processes that data, then depending on the value of a specified cell in each worksheet, sends up to three e-mails (I am referring to this process as 'First E-Mail'); each e-mail sent has a corresponding worksheet attached as an .xlS file. All of this works fine.

I am utilizing an .xlT file that is stored on a shared drive. This file contains, in the 'ThisWorkbook' module, 'BeforeSave Events' code that saves an .xlS file (worksheets are first copied to the .xlT file by the main macro), after it has been modified by an user, to a specified location and also sends an e-mail with such modified file as an attachment (I am referring to this process as 'Second E-Mail').

So, I have created code that will copy each of the three worksheets created by my main macro (depending on the value of a specified cell in each worksheet, there will be 0, 1, 2, or 3 workbooks created). Each copy will be to a corresponding empty worksheet in the .xlT file that contains 'BeforeSave Events' code in the 'ThisWorkbook' module.

This is a snippet, in a 'For i = 1 To 3' loop, from my main macro:

Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("R:PAS IncomeFUND ACCT RETURN E-MAIL.xlT")

wb1.Worksheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)

'DELETE EMPTY WORKSHEET
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

wb2.SaveAs "R:PAS IncomePAS AND-OR ACCT ADJUSTMENTS" & _
"ACCT ADJUST - " & ActiveSheet.Name & " " & Format(myDate, "mm-dd-yy") & ".xlS"

If i = 1 Then fName1 = ActiveWorkbook.Name
If i = 2 Then fName2 = ActiveWorkbook.Name
If i = 3 Then fName3 = ActiveWorkbook.Name

ActiveWorkbook.Close False

Below is my code in the 'ThisWorkbook' module of my .xlT file:

Option Explicit
Dim myPath2 As String
Dim myDate As Variant

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

'IF PATH EXISTS, CODE IS PROCESSED AS A RETURN E-MAIL (SAVES MODIFIED .XLS FILE & E-MAILS SUCH FILE AS ATTACHMENT)
'IF PATH DOES NOT EXIST, THE MAIN MACRO IS CREATING .XLS ATTACHMENT
If ThisWorkbook.Path <> "" Then
myPath2 = "R:PAS IncomePAS AND-OR ACCT ADJUSTMENTS"
myDate = Date - 1

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myPath2 & "COMMENTS - " & ActiveSheet.Name & " " & Format(myDate, "mm-dd-yy") & ".xlS"
Application.DisplayAlerts = True

'THIS CALLS PROCEDURE FOR SENDING SECOND E-MAIL
Call Second_Email

Application.Quit
End

Else
'DO NOTHING
End If

End Sub

A little rationale for this: the main macro sends the First-E-mail with the appropriate workbook as an attachment; the user receives and opens the e-mail; opens the .xlS file; keys in his/her explanation for receipt of such e-mail; clicks on save; and the user is done. That's when the code in 'BeforeSave Events' kicks in, saves the modified .xlS file in the specified location, and sends the Second E-Mail with such modified .xlS file as an attachment.

PROBLEMS:
- In my .xlT file, if I delete the 'ThisWorkbook.SaveAs ...' stmt, everything works just as I intend. However, with such stmt, Excel crashes -- even though Excel crashes, the file is saved in the appropiate location, but no e-mail is sent. Help.
- Variable 'myDate' is defined in my main macro (it is 1, 2, 3, or 4 calendar days prior to Date; this date is next preceding business day). I want to include this variable in the 'ThisWorkbook.SaveAs Filename ...' stmt in my .xlT file; I do not want this variable to change once it is brought into the .xlT file. How can I do this? One potential solution would be to replace the text 'ACCT ADJUST' with the text 'COMMENTS' in the names of the respective files created in the main macro and in the .xlT file. How can I do this? (this would eliminate the need to pass 'myDate' from the .xlA file to the .xlT file; also, since a procedure in my main macro is named 'Auto_Open', if I include a Reference to the .xlA file in the .xlT file, the macro goes berserk).

Jingle123

Hi NG,

In my AddIn I monitor events at an application level (AppEvents As
Excel.Application) in order to trap all the events for certain workbooks.
This works fine.

Within the BeforeSave event, if the passed WB falls into the certain
category, I perform two operations of note before saving the workbook:

(1) - Use wb.SaveCopyAs to create a backup copy. (wb.SaveCopyAs PATH_BACKUP
& "" & myBackUpFileName)
(2) - Create a "copy for publishing", this uses Workbooks.Add to create a
new workbook, populate it with some data, save it elsewhere.

All of this works very nicely, seamlessly and invisibly under normal
circumstances. Today however, I have a new sub in my AddIn which I don't
want to be run unless the user has saved the workbook so I check this and
then, if the user wishes, save it for them before continuing.
code snippet:
If Not ActiveWorkbook.Saved = True Then
If MsgBox("You cannot perform this if the workbook isn't saved." &
vbCrLf _
& "Do you wish to save and continue?", vbOKCancel + vbQuestion)
= vbOK Then
ActiveWorkbook.Save
Else
Exit Sub
End If
End If

The ActiveWorkbook.Save fires my BeforeSave event that I've trapped - as one
would expect - BUT... the SaveCopyAs code doesn't work. I can just F8 over
the line - there's no error raised (err.number = 0) , it's that no copy is
saved.

Likewise when I create the copy for the publishing I use "Set wbNew =
Workbooks.Add" and, instead of creating a new workbook, it just returns the
activeworkbook as wbNew - which rather messes things up.

Obviously the easiest solution is to just warn the user the activeworkbook
isn't saved and exit the procedure rather than offer to save it for them but
this is not what I want to do... :-(

Has anyone seen this sort of behaviour before? All thoughts gratefully
received

Thanks,
Gareth

Excel 2000 SP-3
Win2000


No luck finding an answer? You could always try Google.