Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave event fired but not working when triggered by SaveAs.
Hi,
I have two workbooks, Book1 and Book2, both with macros (see bleow). Here's my problem: If I open only Book2 in Excel and click the save button or choose Save / Save As on the file menu, then the BeforeSave event fires and runs fine, just like expected. So far so good. When I open only Book1 and run the code in the dummy() sub, then when the ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution continues in Book2 and the BeforeSave event fires, just like expected, and all the code in the event handler does runs (I can single step from line to line with F8) BUT none of the instructions do anything; the switch between sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't happen and when the "timestamp" is being written to cell A1 (Ref 2), I get run-time error saying the worksheet is protected. Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave event is fired by the SaveAs instruction?. Same problem if I replace SaveAs with Save. I DO want the code in BeforeSave to run also when the SaveAs instruction executes. I'm using Excel2003 on Win2k and WinXP. Book1 has one worksheet, B1S1, which is password protected Book2 has two worksheets B2S1 and B2S2. The code in Book1 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Sub dummy() Dim wbk As Workbook On Error GoTo errHandler ' Set wbk to reference the opened workbook Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls") ' Switch back to this workbook and do some stuff... ThisWorkbook.Activate Range("A1").Value = Range("A1").Value + 1 ' Switch to the opened workbook, Book2 wbk.Activate ' Disable "Overwrite Y / N / C?" question Application.DisplayAlerts = False ' Save Book2 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" ' (Ref 1) ' Close Book2 wbk.Close ' Enable alerts Application.DisplayAlerts = True Exit Sub errHandler: Debug.Print Err.Number Debug.Print Err.Description End Sub '------------------------------------------------------------------------------- The code in Book2 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Switch to sheet 2 B2S2.Activate ' ' Some more code here.... ' Do some stuff with sheet 2 ' ' Switch back to sheet 1 B2S1.Activate ' Unprotect sheet 1 B2S1.Unprotect "abc" ' Add timestamp to cell A1 Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2) ' Protect sheet 1 B2S1.Protect "abc" End Sub '------------------------------------------------------------------------------- Regards, Anders |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave event fired but not working when triggered by SaveAs.
Maybe I'm missing something, but I don't see where you tell it to open
workbook2. You have wbk.Activate but it does not specify which one, so the logic will tell it to activate ThisWorkbook. Maybe you didn't post the complete code and I'm reading it wrong. "Anders" wrote: Hi, I have two workbooks, Book1 and Book2, both with macros (see bleow). Here's my problem: If I open only Book2 in Excel and click the save button or choose Save / Save As on the file menu, then the BeforeSave event fires and runs fine, just like expected. So far so good. When I open only Book1 and run the code in the dummy() sub, then when the ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution continues in Book2 and the BeforeSave event fires, just like expected, and all the code in the event handler does runs (I can single step from line to line with F8) BUT none of the instructions do anything; the switch between sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't happen and when the "timestamp" is being written to cell A1 (Ref 2), I get run-time error saying the worksheet is protected. Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave event is fired by the SaveAs instruction?. Same problem if I replace SaveAs with Save. I DO want the code in BeforeSave to run also when the SaveAs instruction executes. I'm using Excel2003 on Win2k and WinXP. Book1 has one worksheet, B1S1, which is password protected Book2 has two worksheets B2S1 and B2S2. The code in Book1 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Sub dummy() Dim wbk As Workbook On Error GoTo errHandler ' Set wbk to reference the opened workbook Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls") ' Switch back to this workbook and do some stuff... ThisWorkbook.Activate Range("A1").Value = Range("A1").Value + 1 ' Switch to the opened workbook, Book2 wbk.Activate ' Disable "Overwrite Y / N / C?" question Application.DisplayAlerts = False ' Save Book2 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" ' (Ref 1) ' Close Book2 wbk.Close ' Enable alerts Application.DisplayAlerts = True Exit Sub errHandler: Debug.Print Err.Number Debug.Print Err.Description End Sub '------------------------------------------------------------------------------- The code in Book2 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Switch to sheet 2 B2S2.Activate ' ' Some more code here.... ' Do some stuff with sheet 2 ' ' Switch back to sheet 1 B2S1.Activate ' Unprotect sheet 1 B2S1.Unprotect "abc" ' Add timestamp to cell A1 Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2) ' Protect sheet 1 B2S1.Protect "abc" End Sub '------------------------------------------------------------------------------- Regards, Anders |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave event fired but not working when triggered by SaveAs.
Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
would seem to indicate what wbk refers to. -- Regards, Tom Ogilvy "JLGWhiz" wrote in message ... Maybe I'm missing something, but I don't see where you tell it to open workbook2. You have wbk.Activate but it does not specify which one, so the logic will tell it to activate ThisWorkbook. Maybe you didn't post the complete code and I'm reading it wrong. "Anders" wrote: Hi, I have two workbooks, Book1 and Book2, both with macros (see bleow). Here's my problem: If I open only Book2 in Excel and click the save button or choose Save / Save As on the file menu, then the BeforeSave event fires and runs fine, just like expected. So far so good. When I open only Book1 and run the code in the dummy() sub, then when the ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution continues in Book2 and the BeforeSave event fires, just like expected, and all the code in the event handler does runs (I can single step from line to line with F8) BUT none of the instructions do anything; the switch between sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't happen and when the "timestamp" is being written to cell A1 (Ref 2), I get run-time error saying the worksheet is protected. Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave event is fired by the SaveAs instruction?. Same problem if I replace SaveAs with Save. I DO want the code in BeforeSave to run also when the SaveAs instruction executes. I'm using Excel2003 on Win2k and WinXP. Book1 has one worksheet, B1S1, which is password protected Book2 has two worksheets B2S1 and B2S2. The code in Book1 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Sub dummy() Dim wbk As Workbook On Error GoTo errHandler ' Set wbk to reference the opened workbook Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls") ' Switch back to this workbook and do some stuff... ThisWorkbook.Activate Range("A1").Value = Range("A1").Value + 1 ' Switch to the opened workbook, Book2 wbk.Activate ' Disable "Overwrite Y / N / C?" question Application.DisplayAlerts = False ' Save Book2 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" ' (Ref 1) ' Close Book2 wbk.Close ' Enable alerts Application.DisplayAlerts = True Exit Sub errHandler: Debug.Print Err.Number Debug.Print Err.Description End Sub '------------------------------------------------------------------------------- The code in Book2 (located in ThisWorkbook) is: '------------------------------------------------------------------------------- Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Switch to sheet 2 B2S2.Activate ' ' Some more code here.... ' Do some stuff with sheet 2 ' ' Switch back to sheet 1 B2S1.Activate ' Unprotect sheet 1 B2S1.Unprotect "abc" ' Add timestamp to cell A1 Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2) ' Protect sheet 1 B2S1.Protect "abc" End Sub '------------------------------------------------------------------------------- Regards, Anders |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave event fired but not working when triggered by SaveAs.
Just like Tom says, the "Set wbk = Workbooks.Open...." instruction is what
opens Book2. Note that the code below is a direct copy & paste of a fully working sample that I wrote just for this post to demonstrate the problem. If anyone wants to try it out then create two workbooks called Book1.xls and Book2.xls then copy all code between the dashed lines and paste it in the ThisWorkbook module of the two workbooks. You can save the workbooks anywhere you want as long as both workbooks are in the same directory. Regards, Anders "Tom Ogilvy" wrote: Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls") would seem to indicate what wbk refers to. -- Regards, Tom Ogilvy "JLGWhiz" wrote in message ... Maybe I'm missing something, but I don't see where you tell it to open workbook2. You have wbk.Activate but it does not specify which one, so the logic will tell it to activate ThisWorkbook. Maybe you didn't post the complete code and I'm reading it wrong. "Anders" wrote: Hi, I have two workbooks, Book1 and Book2, both with macros (see bleow). Here's my problem: If I open only Book2 in Excel and click the save button or choose Save / Save As on the file menu, then the BeforeSave event fires and runs fine, just like expected. So far so good. When I open only Book1 and run the code in the dummy() sub, then when the ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution continues in Book2 and the BeforeSave event fires, just like expected, and all the code in the event handler does runs (I can single step from line to line with F8) BUT none of the instructions do anything; the switch between sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't happen and when the "timestamp" is being written to cell A1 (Ref 2), I get run-time error saying the worksheet is protected. Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave event is fired by the SaveAs instruction?. Same problem if I replace SaveAs with Save. I DO want the code in BeforeSave to run also when the SaveAs instruction executes. I'm using Excel2003 on Win2k and WinXP. Book1 has one worksheet, B1S1, which is password protected Book2 has two worksheets B2S1 and B2S2. The code in Book1 (located in ThisWorkbook) is: '------------- Begin Copy - Book1.xls ------------------------------------------ Option Explicit Sub dummy() Dim wbk As Workbook On Error GoTo errHandler ' Set wbk to refere to the opened Book2 workbook Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls") ' Switch back to this workbook and do some stuff... ThisWorkbook.Activate Range("A1").Value = Range("A1").Value + 1 ' Switch to the opened workbook, Book2 wbk.Activate ' Disable "Overwrite Y / N / C?" question Application.DisplayAlerts = False ' Save Book2 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" ' (Ref 1) ' Close Book2 wbk.Close ' Enable alerts Application.DisplayAlerts = True Exit Sub errHandler: Debug.Print Err.Number Debug.Print Err.Description End Sub '------------- End Copy - Book1.xls -------------------------------------------- The code in Book2 (located in ThisWorkbook) is: '------------- Begin Copy - Book2.xls ------------------------------------------ Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Switch to sheet 2 B2S2.Activate ' ' Some more code here.... ' Do some stuff with sheet 2 ' ' Switch back to sheet 1 B2S1.Activate ' Unprotect sheet 1 B2S1.Unprotect "abc" ' Add timestamp to cell A1 Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2) ' Protect sheet 1 B2S1.Protect "abc" End Sub '------------- End Copy - Book2.xls -------------------------------------------- Regards, Anders |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No event fired on sort | Excel Programming | |||
Detecting Save vs SaveAs in BeforeSave | Excel Programming | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming | |||
Cannot get appropriate Event fired with WinSock control | Excel Programming |