Beforesave issue

[Boozer]

I have written some code that saves a different workbook based on date. However, the workbook that I am trying to save has its own beforesave code in it. When I run my code to save the specified workbook, it runs it beforesave procedure, as it should. But the code in that procedure has no effect. It won't select cells or unprotect the worksheet or activate different sheets. I tried remarking out all the code in the beforesave procedure, so that the procedure was called, but didnt do anything. When I did this, my SaveAs command would execute, but would not actually save the workbook. Any suggestions?

[Colo]

Hi Boozer,

Could you show us your code?

[Boozer]

How's this for example. BTW I am using Excel '97.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/21/02 by NCC
' This is the main macro

'
ActiveWorkbook.SaveAs FileName:="C:WINNTProfilesnccDesktopHelp.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Before it saves the file, the following BeforeSave code runs, but will NOT select Sheet2 to put the value in. The "help" will be placed on Sheet1 in A1.The code works by itself, just not in conjuction with the SaveAs code in the macro.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet2").Select
Range("A1").Value = "help"
End Sub

[Boozer]

Any ideas?

[brettvba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet2").Range("A1").Value = "help"
End Sub

try that there is no reason why the sheet should not be selected
if this doesn't help post back

[Boozer]

Okay, that puts the "help" on sheet2, but unfortunately, I did not write my previous code that way I didn't think that by adding SaveAs code to the project that I would have to rewrite my all my previous code. Could someone tell me why, by adding the SaveAs code, the BeforeSave code will not activate the sheet or protect/unprotect the active workbook (EX. ActiveSheet.Protect has no effect in the BeforeSave code if this code is called by the SaveAs code)?

[brettvba]

HI,
Don't really understand what your asking sounds like a new question if your code is not working post it and I will have a look!.

[Boozer]

What I am trying to say is... Lets say you have the following code in Excel '97 that simply saves a file.

Sub Problem()
ActiveWorkbook.SaveAs FileName:="C:\WINNT\Profiles\ncc\Desktop\Help.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Now that works fine and dandy, UNLESS you have BeforeSave code in the workbook such as this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet2").Range("A1").Value = "help" ' This works and puts "help" in cell A1 on sheet2

Sheets("Sheet2").Select
Range("A1").Value = "help"
'the previous two statements should work the same, but sheet2 is not selected and "help" is placed on sheet1

ActiveSheet.Protect 'This has no effect on the sheet in the BeforeSave macro. The sheet is not protected, and if you try to unprotect a protected worksheet, that does not work either. If one steps through the code, it passes through the code like it worked correctly, but if you go look at the sheet, it is not protected/unprotected (whichever one is trying to do).

End Sub

I only encouter this problem when a SaveAs code is combined with BeforeSave code. The SaveAs code works fine by itself. And the BeforeSave code works fine by itself. But throw the two together and the BeforeSave code seems to have almost no effect.

[Tom Schreiner]

I'm guessing that your BeforeSave event is not firing because the SaveAs creates a new workbook and does not save the calling workbook. The before save event is a workbook level event. If the workbook is newly created, it is not going to fire the event in the calling workbook. You will need to capture this event at the application level. In order to do this, you will need to create an application class of your own. Please see this link for a better explanation of using application level events. I already tested it for your purposes and it works fine.

http://www.cpearson.com/excel/AppEvent.htm

Tom

[Boozer]

So do I need both the BeforeSave and SaveAs code at the Application level?