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???

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???

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???

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???

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???

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???

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???