I have a single sheet workbook open, with Event code
in the ThisWorkbook module. No other modules.

User makes changes to permitted fields in the sheet
and then attempts to Save. Code runs which creates
a new book, copies the sheet to it (thus not copying
the Thisworkbook code), saves the book with the user's
new name, and leaves it open.

I now remove most of the user's changes to the original
Worksheet (the original book still being open) and then
attempt to close the original using this code:

With Workbooks(wkbkname).Worksheets("Master Order")
.Unprotect Password:= "abc"
.Range("H11").ClearContents
.Range("A2:E15").ClearContents
.Range("A31:K46").ClearContents
.Range("A50:K53").ClearContents
.Range("A57:K63").ClearContents
.Protect Password:= "abc"
End With
Application.EnableEvents = True 'Line1
Workbooks(wkbkname).Close SaveChanges:=True
Application.ScreenUpdating = True
End If
Application.EnableEvents = True
End Sub

When the Close code line runs, the code jumps to the
Before_Close routine which says:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
Application.EnableEvents = True
End Sub

and the changes are not saved.

If I change the line marked as 'Line1 to
Application.EnableEvents = False
the the changes will stick, but Events are turned off.

What can I do, please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
Application.EnableEvents = True
End Sub

Just get rid of this code. The first line tells Excel there are no changes
so the Workbook.close doesn't save. The second line is superfluous since
events have to be enabled for this code to be called in the first place.

--
Jim Rech
Excel MVP
"Stuart" wrote in message
...
|I have a single sheet workbook open, with Event code
| in the ThisWorkbook module. No other modules.
|
| User makes changes to permitted fields in the sheet
| and then attempts to Save. Code runs which creates
| a new book, copies the sheet to it (thus not copying
| the Thisworkbook code), saves the book with the user's
| new name, and leaves it open.
|
| I now remove most of the user's changes to the original
| Worksheet (the original book still being open) and then
| attempt to close the original using this code:
|
| With Workbooks(wkbkname).Worksheets("Master Order")
| .Unprotect Password:= "abc"
| .Range("H11").ClearContents
| .Range("A2:E15").ClearContents
| .Range("A31:K46").ClearContents
| .Range("A50:K53").ClearContents
| .Range("A57:K63").ClearContents
| .Protect Password:= "abc"
| End With
| Application.EnableEvents = True 'Line1
| Workbooks(wkbkname).Close SaveChanges:=True
| Application.ScreenUpdating = True
| End If
| Application.EnableEvents = True
| End Sub
|
| When the Close code line runs, the code jumps to the
| Before_Close routine which says:
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisWorkbook.Saved = True
| Application.EnableEvents = True
| End Sub
|
| and the changes are not saved.
|
| If I change the line marked as 'Line1 to
| Application.EnableEvents = False
| the the changes will stick, but Events are turned off.
|
| What can I do, please?
|
| Regards.
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
|
|

Many thanks.

The Before_Close Event code was an attempt to allow
the user to click the little 'x' (top right) and Cancel.
By that I mean that if part way through and having
made some changes, they decided to Cancel, then
the workbook would close without any changes being
saved.

What is the correct way to cater for that eventuality,
please?

Regards.

"Jim Rech" wrote in message
...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
Application.EnableEvents = True
End Sub

Just get rid of this code. The first line tells Excel there are no
changes
so the Workbook.close doesn't save. The second line is superfluous since
events have to be enabled for this code to be called in the first place.

--
Jim Rech
Excel MVP
"Stuart" wrote in message
...
|I have a single sheet workbook open, with Event code
| in the ThisWorkbook module. No other modules.
|
| User makes changes to permitted fields in the sheet
| and then attempts to Save. Code runs which creates
| a new book, copies the sheet to it (thus not copying
| the Thisworkbook code), saves the book with the user's
| new name, and leaves it open.
|
| I now remove most of the user's changes to the original
| Worksheet (the original book still being open) and then
| attempt to close the original using this code:
|
| With Workbooks(wkbkname).Worksheets("Master Order")
| .Unprotect Password:= "abc"
| .Range("H11").ClearContents
| .Range("A2:E15").ClearContents
| .Range("A31:K46").ClearContents
| .Range("A50:K53").ClearContents
| .Range("A57:K63").ClearContents
| .Protect Password:= "abc"
| End With
| Application.EnableEvents = True 'Line1
| Workbooks(wkbkname).Close SaveChanges:=True
| Application.ScreenUpdating = True
| End If
| Application.EnableEvents = True
| End Sub
|
| When the Close code line runs, the code jumps to the
| Before_Close routine which says:
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisWorkbook.Saved = True
| Application.EnableEvents = True
| End Sub
|
| and the changes are not saved.
|
| If I change the line marked as 'Line1 to
| Application.EnableEvents = False
| the the changes will stick, but Events are turned off.
|
| What can I do, please?
|
| Regards.
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
|
|




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

In the main save routine I'd check the .Saved property and if it's false I'd
ask the user to save changes with my own msgbox. If yes I'd do the copy to
and save of the data workbook. If no I'd just close. In any case I'm not
clear on why you are clearing all those cells in the master. If you just
close with 'save changes:=false' wouldn't you be in the same place as when
you started? (Presumably with a "clear" workbook?

--
Jim Rech
Excel MVP
"Stuart" wrote in message
...
| Many thanks.
|
| The Before_Close Event code was an attempt to allow
| the user to click the little 'x' (top right) and Cancel.
| By that I mean that if part way through and having
| made some changes, they decided to Cancel, then
| the workbook would close without any changes being
| saved.
|
| What is the correct way to cater for that eventuality,
| please?
|
| Regards.
|
| "Jim Rech" wrote in message
| ...
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisWorkbook.Saved = True
| Application.EnableEvents = True
| End Sub
|
| Just get rid of this code. The first line tells Excel there are no
| changes
| so the Workbook.close doesn't save. The second line is superfluous
since
| events have to be enabled for this code to be called in the first place.
|
| --
| Jim Rech
| Excel MVP
| "Stuart" wrote in message
| ...
| |I have a single sheet workbook open, with Event code
| | in the ThisWorkbook module. No other modules.
| |
| | User makes changes to permitted fields in the sheet
| | and then attempts to Save. Code runs which creates
| | a new book, copies the sheet to it (thus not copying
| | the Thisworkbook code), saves the book with the user's
| | new name, and leaves it open.
| |
| | I now remove most of the user's changes to the original
| | Worksheet (the original book still being open) and then
| | attempt to close the original using this code:
| |
| | With Workbooks(wkbkname).Worksheets("Master Order")
| | .Unprotect Password:= "abc"
| | .Range("H11").ClearContents
| | .Range("A2:E15").ClearContents
| | .Range("A31:K46").ClearContents
| | .Range("A50:K53").ClearContents
| | .Range("A57:K63").ClearContents
| | .Protect Password:= "abc"
| | End With
| | Application.EnableEvents = True 'Line1
| | Workbooks(wkbkname).Close SaveChanges:=True
| | Application.ScreenUpdating = True
| | End If
| | Application.EnableEvents = True
| | End Sub
| |
| | When the Close code line runs, the code jumps to the
| | Before_Close routine which says:
| |
| | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| | ThisWorkbook.Saved = True
| | Application.EnableEvents = True
| | End Sub
| |
| | and the changes are not saved.
| |
| | If I change the line marked as 'Line1 to
| | Application.EnableEvents = False
| | the the changes will stick, but Events are turned off.
| |
| | What can I do, please?
| |
| | Regards.
| |
| |
| | ---
| | Outgoing mail is certified Virus Free.
| | Checked by AVG anti-virus system (http://www.grisoft.com).
| | Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
| |
| |
|
|
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
|
|

Thanks for that. I'll work with your suggestions.

Why clear certain cells and then save as True? Well,
one cell contains an order number, and this needs to
increment.
So by the time this code is reached, user has committed
a save, the new workbook has been created and saved
(and it is open and stripped of code).
With the original workbook, I now remove all the user's
new entries and force save changes as true, such that
the new order number is preserved.

If user next opens the original file, then the order
number is incremented in the Workbook_Open Event

Background: I'm tasked with standardising Company
documents eg: Orders, Fax, etc.
I've users who are "uncooperative"....they prefer their
own creativity! Nevertheless it will happen.

With this early code I'm trying to show the advantages
to the user (eg automatic insertion of the current date,
saving standard data.....saves typing it every time etc).

When they're on my side (hopefuuly soon) then I'm
thinking an addin will be the best route.

BTW I'm with XL2000 and they have 2003.
At the moment they face the macro warning message
when code runs. If I develop the addin under 2000
as opposed to 2003, would there be issues viz-a
viz Certification?

Many thanks for your help.

Regards

"Jim Rech" wrote in message
...
In the main save routine I'd check the .Saved property and if it's false
I'd
ask the user to save changes with my own msgbox. If yes I'd do the copy
to
and save of the data workbook. If no I'd just close. In any case I'm not
clear on why you are clearing all those cells in the master. If you just
close with 'save changes:=false' wouldn't you be in the same place as when
you started? (Presumably with a "clear" workbook?

--
Jim Rech
Excel MVP
"Stuart" wrote in message
...
| Many thanks.
|
| The Before_Close Event code was an attempt to allow
| the user to click the little 'x' (top right) and Cancel.
| By that I mean that if part way through and having
| made some changes, they decided to Cancel, then
| the workbook would close without any changes being
| saved.
|
| What is the correct way to cater for that eventuality,
| please?
|
| Regards.
|
| "Jim Rech" wrote in message
| ...
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisWorkbook.Saved = True
| Application.EnableEvents = True
| End Sub
|
| Just get rid of this code. The first line tells Excel there are no
| changes
| so the Workbook.close doesn't save. The second line is superfluous
since
| events have to be enabled for this code to be called in the first
place.
|
| --
| Jim Rech
| Excel MVP
| "Stuart" wrote in message
| ...
| |I have a single sheet workbook open, with Event code
| | in the ThisWorkbook module. No other modules.
| |
| | User makes changes to permitted fields in the sheet
| | and then attempts to Save. Code runs which creates
| | a new book, copies the sheet to it (thus not copying
| | the Thisworkbook code), saves the book with the user's
| | new name, and leaves it open.
| |
| | I now remove most of the user's changes to the original
| | Worksheet (the original book still being open) and then
| | attempt to close the original using this code:
| |
| | With Workbooks(wkbkname).Worksheets("Master Order")
| | .Unprotect Password:= "abc"
| | .Range("H11").ClearContents
| | .Range("A2:E15").ClearContents
| | .Range("A31:K46").ClearContents
| | .Range("A50:K53").ClearContents
| | .Range("A57:K63").ClearContents
| | .Protect Password:= "abc"
| | End With
| | Application.EnableEvents = True 'Line1
| | Workbooks(wkbkname).Close SaveChanges:=True
| | Application.ScreenUpdating = True
| | End If
| | Application.EnableEvents = True
| | End Sub
| |
| | When the Close code line runs, the code jumps to the
| | Before_Close routine which says:
| |
| | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| | ThisWorkbook.Saved = True
| | Application.EnableEvents = True
| | End Sub
| |
| | and the changes are not saved.
| |
| | If I change the line marked as 'Line1 to
| | Application.EnableEvents = False
| | the the changes will stick, but Events are turned off.
| |
| | What can I do, please?
| |
| | Regards.
| |
| |
| | ---
| | Outgoing mail is certified Virus Free.
| | Checked by AVG anti-virus system (http://www.grisoft.com).
| | Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
| |
| |
|
|
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
|
|




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

If I develop the addin under 2000 as opposed to 2003, would there be
issues viz-a viz Certification?

Probably not but I'm not sure.

Too bad you can't have an external "next number" so the workbook doesn't
have to be saved. Like saved in its own little Excel file. Hmm...


--
Jim Rech
Excel MVP
"Stuart" wrote in message
...
| Thanks for that. I'll work with your suggestions.
|
| Why clear certain cells and then save as True? Well,
| one cell contains an order number, and this needs to
| increment.
| So by the time this code is reached, user has committed
| a save, the new workbook has been created and saved
| (and it is open and stripped of code).
| With the original workbook, I now remove all the user's
| new entries and force save changes as true, such that
| the new order number is preserved.
|
| If user next opens the original file, then the order
| number is incremented in the Workbook_Open Event
|
| Background: I'm tasked with standardising Company
| documents eg: Orders, Fax, etc.
| I've users who are "uncooperative"....they prefer their
| own creativity! Nevertheless it will happen.
|
| With this early code I'm trying to show the advantages
| to the user (eg automatic insertion of the current date,
| saving standard data.....saves typing it every time etc).
|
| When they're on my side (hopefuuly soon) then I'm
| thinking an addin will be the best route.
|
| BTW I'm with XL2000 and they have 2003.
| At the moment they face the macro warning message
| when code runs. If I develop the addin under 2000
| as opposed to 2003, would there be issues viz-a
| viz Certification?
|
| Many thanks for your help.
|
| Regards
|
| "Jim Rech" wrote in message
| ...
| In the main save routine I'd check the .Saved property and if it's false
| I'd
| ask the user to save changes with my own msgbox. If yes I'd do the copy
| to
| and save of the data workbook. If no I'd just close. In any case I'm
not
| clear on why you are clearing all those cells in the master. If you
just
| close with 'save changes:=false' wouldn't you be in the same place as
when
| you started? (Presumably with a "clear" workbook?
|
| --
| Jim Rech
| Excel MVP
| "Stuart" wrote in message
| ...
| | Many thanks.
| |
| | The Before_Close Event code was an attempt to allow
| | the user to click the little 'x' (top right) and Cancel.
| | By that I mean that if part way through and having
| | made some changes, they decided to Cancel, then
| | the workbook would close without any changes being
| | saved.
| |
| | What is the correct way to cater for that eventuality,
| | please?
| |
| | Regards.
| |
| | "Jim Rech" wrote in message
| | ...
| | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| | ThisWorkbook.Saved = True
| | Application.EnableEvents = True
| | End Sub
| |
| | Just get rid of this code. The first line tells Excel there are no
| | changes
| | so the Workbook.close doesn't save. The second line is superfluous
| since
| | events have to be enabled for this code to be called in the first
| place.
| |
| | --
| | Jim Rech
| | Excel MVP
| | "Stuart" wrote in message
| | ...
| | |I have a single sheet workbook open, with Event code
| | | in the ThisWorkbook module. No other modules.
| | |
| | | User makes changes to permitted fields in the sheet
| | | and then attempts to Save. Code runs which creates
| | | a new book, copies the sheet to it (thus not copying
| | | the Thisworkbook code), saves the book with the user's
| | | new name, and leaves it open.
| | |
| | | I now remove most of the user's changes to the original
| | | Worksheet (the original book still being open) and then
| | | attempt to close the original using this code:
| | |
| | | With Workbooks(wkbkname).Worksheets("Master Order")
| | | .Unprotect Password:= "abc"
| | | .Range("H11").ClearContents
| | | .Range("A2:E15").ClearContents
| | | .Range("A31:K46").ClearContents
| | | .Range("A50:K53").ClearContents
| | | .Range("A57:K63").ClearContents
| | | .Protect Password:= "abc"
| | | End With
| | | Application.EnableEvents = True 'Line1
| | | Workbooks(wkbkname).Close SaveChanges:=True
| | | Application.ScreenUpdating = True
| | | End If
| | | Application.EnableEvents = True
| | | End Sub
| | |
| | | When the Close code line runs, the code jumps to the
| | | Before_Close routine which says:
| | |
| | | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| | | ThisWorkbook.Saved = True
| | | Application.EnableEvents = True
| | | End Sub
| | |
| | | and the changes are not saved.
| | |
| | | If I change the line marked as 'Line1 to
| | | Application.EnableEvents = False
| | | the the changes will stick, but Events are turned off.
| | |
| | | What can I do, please?
| | |
| | | Regards.
| | |
| | |
| | | ---
| | | Outgoing mail is certified Virus Free.
| | | Checked by AVG anti-virus system (http://www.grisoft.com).
| | | Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
| | |
| | |
| |
| |
| |
| |
| | ---
| | Outgoing mail is certified Virus Free.
| | Checked by AVG anti-virus system (http://www.grisoft.com).
| | Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
| |
| |
|
|
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004
|
|