Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

Yes, that is the proper module to have it in.

But first time you save or close, the workbook will close without saving the
code. My mistake in not explaining this.

Steps to take................

Open the workbook.

Paste the code into Thisworkbook module.

ViewImmediate Window.

Type in Application.EnableEvents = False then ENTER key

FileSave the workbook. You will be asked if you want to save changes.

Yes, you do.

Back to the Immediate Window and Application.EnableEvents = True.

Your code when closing or saving the workbook should now work properly.


Gord

On Sun, 9 Dec 2007 15:18:00 -0800, Silena K-K
wrote:

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

Hi Gord

Still doesn't work.

At the step "FileSave the workbook" I don't get ask if I want to save
changes and the "Application.EnableEvents" does not change from False to True.

Silena

"Gord Dibben" wrote:

Yes, that is the proper module to have it in.

But first time you save or close, the workbook will close without saving the
code. My mistake in not explaining this.

Steps to take................

Open the workbook.

Paste the code into Thisworkbook module.

ViewImmediate Window.

Type in Application.EnableEvents = False then ENTER key

FileSave the workbook. You will be asked if you want to save changes.

Yes, you do.

Back to the Immediate Window and Application.EnableEvents = True.

Your code when closing or saving the workbook should now work properly.


Gord

On Sun, 9 Dec 2007 15:18:00 -0800, Silena K-K
wrote:

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

You have to manually change Application.EnableEvents to True

I'm off now for horses hooves and libations so won't get back untill tomorrow.


Gord


On Sun, 9 Dec 2007 16:24:00 -0800, Silena K-K
wrote:

Hi Gord

Still doesn't work.

At the step "FileSave the workbook" I don't get ask if I want to save
changes and the "Application.EnableEvents" does not change from False to True.

Silena

"Gord Dibben" wrote:

Yes, that is the proper module to have it in.

But first time you save or close, the workbook will close without saving the
code. My mistake in not explaining this.

Steps to take................

Open the workbook.

Paste the code into Thisworkbook module.

ViewImmediate Window.

Type in Application.EnableEvents = False then ENTER key

FileSave the workbook. You will be asked if you want to save changes.

Yes, you do.

Back to the Immediate Window and Application.EnableEvents = True.

Your code when closing or saving the workbook should now work properly.


Gord

On Sun, 9 Dec 2007 15:18:00 -0800, Silena K-K
wrote:

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

Hi Gord

I followed your steps and have the following code in VB but now when I use
"ctrl s" or F12 to test that the save doesn't work it crashes Excel. Any
ideas why? Silena

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close

End Sub

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

End Sub



"Gord Dibben" wrote:

You have to manually change Application.EnableEvents to True

I'm off now for horses hooves and libations so won't get back untill tomorrow.


Gord


On Sun, 9 Dec 2007 16:24:00 -0800, Silena K-K
wrote:

Hi Gord

Still doesn't work.

At the step "FileSave the workbook" I don't get ask if I want to save
changes and the "Application.EnableEvents" does not change from False to True.

Silena

"Gord Dibben" wrote:

Yes, that is the proper module to have it in.

But first time you save or close, the workbook will close without saving the
code. My mistake in not explaining this.

Steps to take................

Open the workbook.

Paste the code into Thisworkbook module.

ViewImmediate Window.

Type in Application.EnableEvents = False then ENTER key

FileSave the workbook. You will be asked if you want to save changes.

Yes, you do.

Back to the Immediate Window and Application.EnableEvents = True.

Your code when closing or saving the workbook should now work properly.


Gord

On Sun, 9 Dec 2007 15:18:00 -0800, Silena K-K
wrote:

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena

I cannot replicate your crashing problem with CTRL + s and F12

Did you reenable events after saving the workbook with the code?

I would presume so.

What happens with FileSave?

What happens when you click on the "Save" Icon on Toolbar?

What happens when you just Close the workbook?

If you want to send me the workbook to me via email I can have a look.

Change the AT and DOT to appropriate punctuation in my posting address.


Gord


On Mon, 10 Dec 2007 12:21:16 -0800, Silena K-K
wrote:

Hi Gord

I followed your steps and have the following code in VB but now when I use
"ctrl s" or F12 to test that the save doesn't work it crashes Excel. Any
ideas why? Silena

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close

End Sub

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

End Sub



"Gord Dibben" wrote:

You have to manually change Application.EnableEvents to True

I'm off now for horses hooves and libations so won't get back untill tomorrow.


Gord


On Sun, 9 Dec 2007 16:24:00 -0800, Silena K-K
wrote:

Hi Gord

Still doesn't work.

At the step "FileSave the workbook" I don't get ask if I want to save
changes and the "Application.EnableEvents" does not change from False to True.

Silena

"Gord Dibben" wrote:

Yes, that is the proper module to have it in.

But first time you save or close, the workbook will close without saving the
code. My mistake in not explaining this.

Steps to take................

Open the workbook.

Paste the code into Thisworkbook module.

ViewImmediate Window.

Type in Application.EnableEvents = False then ENTER key

FileSave the workbook. You will be asked if you want to save changes.

Yes, you do.

Back to the Immediate Window and Application.EnableEvents = True.

Your code when closing or saving the workbook should now work properly.


Gord

On Sun, 9 Dec 2007 15:18:00 -0800, Silena K-K
wrote:

Hi Gord

I have tried this and it doesn't seem to work - although it's highly likely
that I'm not writing it into VB correctly.

I copied the text below from "Private Sub" to "End Sub" and pasted it into
VB by selecting Tools, Macros, VB Editor and then double clicking on the
"This workbook" module and pasting the code into the blank screen.

Is that correct??

Silena


"Gord Dibben" wrote:

Entered into Thisworkbook module to cover user hitting the Save Icon or
FileSave.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

Workbook will close with no save taking place because you have faked it out with
"I'm already saved".

You may want to also put code into the BeforeClose event.to cover them just
closing. Workbook will close with no save or alert.

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


Gord Dibben MS Excel MVP

On Sun, 9 Dec 2007 13:52:01 -0800, Silena K-K
wrote:

Hi

I have created a worksheet form in Excel which will be used as a template
and want to be able to disable the save command.

The idea is that the users enter their data, print out the results and then
close the form without it saving. We want the form to automatically close
without saving, not ask the user whether they want to save or not.

Is there a macro command and/or code that can do that??

Thanks and Merry Christmas. Silena