Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
I have a regular sub where the user is given the option to save; if they
enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Is it erroring on? Have you stepped through it and seen where it happens?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paige" wrote in message ... I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
If you have On Error Resume Next comment it out to see if you are generating
errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
I never get any error messages (and don't have any on error resume next).
When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Paige,
Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will
get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
P.S. The file save verbiage that I have in a standard module is as follows:
MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Hi Paige -
Your code works perfectly on my machine. The only thing I didn't see in the BeforeSave event code you sent was an "End Sub" statement at the end. I'm just assuming you missed it when copying the code, but we need to make sure before proceeding. Without it, the code stalls with an error when compiling - not exactly the behavior you describe, but we need to eliminate all possibilities... Otherwise, what version of Windows and Excel are you running? Your code works properly on my WinXP/Excel2003 installation. -- Jay "Paige" wrote: P.S. The file save verbiage that I have in a standard module is as follows: MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Hi, Jay. Re the 'end sub', I just missed copying it over; sorry. Am using
WinXP/Excel 2003 also. Let me ask a theory question. When I put in breakpoints and run through the code, does Excel REALLY process each line of code in the same order that I see it stepped through in terms of when the before_save event is triggered? Or is there some behind the scenes stuff going on in terms of order of processing that wouldn't be readily apparent to me? Hope this is not a totally stupid question. "Jay" wrote: Hi Paige - Your code works perfectly on my machine. The only thing I didn't see in the BeforeSave event code you sent was an "End Sub" statement at the end. I'm just assuming you missed it when copying the code, but we need to make sure before proceeding. Without it, the code stalls with an error when compiling - not exactly the behavior you describe, but we need to eliminate all possibilities... Otherwise, what version of Windows and Excel are you running? Your code works properly on my WinXP/Excel2003 installation. -- Jay "Paige" wrote: P.S. The file save verbiage that I have in a standard module is as follows: MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Hi Paige - Stop the presses! Here's what's wrong along with a solution.
Your code executes perfectly; just not on the right workbook. The cause of the apparent 'coma' is that your BeforeSave event procedure manipulates the original workbook, not the "new" workbook that is being created. The SaveAs method does fire the BeforeSave event in the original workbook as you discovered, but while you are stepping through that procedure, the statements are operating on 'itself' because the procedure is in the ThisWorkbook module, but what is visible on screen is the new workbook you're creating. This results in the illusion of the coma. After the whole procedure terminates, the original workbook closes in the background without saving and you're left with the newly saved workbook (with no protection or copied cells). A solution is to blend the Code from the BeforeSave event with the code sample that starts the process. After doing this, don't forget to delete the BeforeSave event procedure; it's no longer necessary. Here's the blended solution: --------------------------------------------------------------------------------------------- Sub StartSave() If MsgBox("Save the file now?", vbYesNo) = vbYes Then 'Start of your original BeforeSave code Application.EnableEvents = True Application.ActiveWorkbook.Worksheets("SNT").Prote ct Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Application.ActiveWorkbook.Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Application.ActiveWorkbook.Worksheets("Instruction s").Select Application.ActiveWorkbook.ActiveSheet.Range("H203 :AJ204").Select Selection.Copy Application.ActiveWorkbook.ActiveSheet.Range("H212 :AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False 'End of your original BeforeSave code Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", _ FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If End If End Sub -- Jay "Paige" wrote: Hi, Jay. Re the 'end sub', I just missed copying it over; sorry. Am using WinXP/Excel 2003 also. Let me ask a theory question. When I put in breakpoints and run through the code, does Excel REALLY process each line of code in the same order that I see it stepped through in terms of when the before_save event is triggered? Or is there some behind the scenes stuff going on in terms of order of processing that wouldn't be readily apparent to me? Hope this is not a totally stupid question. "Jay" wrote: Hi Paige - Your code works perfectly on my machine. The only thing I didn't see in the BeforeSave event code you sent was an "End Sub" statement at the end. I'm just assuming you missed it when copying the code, but we need to make sure before proceeding. Without it, the code stalls with an error when compiling - not exactly the behavior you describe, but we need to eliminate all possibilities... Otherwise, what version of Windows and Excel are you running? Your code works properly on my WinXP/Excel2003 installation. -- Jay "Paige" wrote: P.S. The file save verbiage that I have in a standard module is as follows: MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
Paige - It's of no funtional consequence, but this statement is incorrect in
may 'solution' post: "...but what is visible on screen is the new workbook you're creating." You actually see the original file (until the very end of the procedure), but the screen is static so you don't see any activity (the coma). Regardless, the solution still applies. I just wanted to correct that error in my description. -- Jay "Paige" wrote: Hi, Jay. Re the 'end sub', I just missed copying it over; sorry. Am using WinXP/Excel 2003 also. Let me ask a theory question. When I put in breakpoints and run through the code, does Excel REALLY process each line of code in the same order that I see it stepped through in terms of when the before_save event is triggered? Or is there some behind the scenes stuff going on in terms of order of processing that wouldn't be readily apparent to me? Hope this is not a totally stupid question. "Jay" wrote: Hi Paige - Your code works perfectly on my machine. The only thing I didn't see in the BeforeSave event code you sent was an "End Sub" statement at the end. I'm just assuming you missed it when copying the code, but we need to make sure before proceeding. Without it, the code stalls with an error when compiling - not exactly the behavior you describe, but we need to eliminate all possibilities... Otherwise, what version of Windows and Excel are you running? Your code works properly on my WinXP/Excel2003 installation. -- Jay "Paige" wrote: P.S. The file save verbiage that I have in a standard module is as follows: MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before_Save IS KILLING ME
I'm flabbergasted. This seemingly small little tidbit obviously has drastic
implications; in all my readings, I don't recall this being explained. It works correctly now. Thank you thank you thank you thank you!!!!!! You're a genius, and thanks for sticking with me on this one! "Jay" wrote: Paige - It's of no funtional consequence, but this statement is incorrect in may 'solution' post: "...but what is visible on screen is the new workbook you're creating." You actually see the original file (until the very end of the procedure), but the screen is static so you don't see any activity (the coma). Regardless, the solution still applies. I just wanted to correct that error in my description. -- Jay "Paige" wrote: Hi, Jay. Re the 'end sub', I just missed copying it over; sorry. Am using WinXP/Excel 2003 also. Let me ask a theory question. When I put in breakpoints and run through the code, does Excel REALLY process each line of code in the same order that I see it stepped through in terms of when the before_save event is triggered? Or is there some behind the scenes stuff going on in terms of order of processing that wouldn't be readily apparent to me? Hope this is not a totally stupid question. "Jay" wrote: Hi Paige - Your code works perfectly on my machine. The only thing I didn't see in the BeforeSave event code you sent was an "End Sub" statement at the end. I'm just assuming you missed it when copying the code, but we need to make sure before proceeding. Without it, the code stalls with an error when compiling - not exactly the behavior you describe, but we need to eliminate all possibilities... Otherwise, what version of Windows and Excel are you running? Your code works properly on my WinXP/Excel2003 installation. -- Jay "Paige" wrote: P.S. The file save verbiage that I have in a standard module is as follows: MsgBox ("Save the file now; otherwise, select 'Cancel'.") Application.EnableEvents = True FName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls") If FName < False Then Application.EnableEvents = True ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal End If "Paige" wrote: Thanks, Jay; yes, it does appear. Below is the sub in ThisWorkbook. It will get to this sub, but doesn't reprotect the two sheets or copy the data in the Instructions tab; but I don't get any error message either. If I put the code into a standard module and run it, it works fine. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = True Worksheets("SNT").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("SNT OS").Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoRestrictions Worksheets("Instructions").Unprotect Password:="xxx" Worksheets("Instructions").Select Range("H203:AJ204").Select Selection.Copy Range("H212:AJ213").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Jay" wrote: Paige, Reduce your BeforeSave event procedure and let us know if it the message box appears: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) msgbox "The messagebox statement works..." End Sub -- Jay "Paige" wrote: I never get any error messages (and don't have any on error resume next). When I step thru the code via breakpoint, it just goes over to the before_save and thru all the lines of code, but doesn't act on them. Am at a loss. "Jim Thomlinson" wrote: If you have On Error Resume Next comment it out to see if you are generating errors that are being ignored... -- HTH... Jim Thomlinson "Paige" wrote: I have a regular sub where the user is given the option to save; if they enter a filename, then I want Excel to go to a specific tab and copy the values from one range and paste to another range (same tab) prior to the save. Excel gets over to the before save sub in ThisWorkbook okay, but it does none of the events in the before save sub. I've put in breakpoints, and when stepping through, it just goes through the lines of code in the before save sub, but doesn't actually act on any of them, like it went into a coma. I'm at my wits end as to why this is happening; have tried everything I can think of, but still it does not work. Enable events is on. Can someone enlighten me PLEASE??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
before_save problems | Excel Programming | |||
Problem with Before_Save Macro | Excel Discussion (Misc queries) | |||
Keep Before_Save from running | Excel Programming | |||
Before_Save event | Excel Programming | |||
Need Before_Save code | Excel Programming |