I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

Assign a cell, say Z100 as a "memory". You deliver the workbook with the
cell empty. Include a small BeforeSave macro to set Z100 to 1.

Also include a WorkbookOpen macro. The workbookOpen macro should examine
Z100. If Z100 is blank, the other macros are called. However if Z100 is set
to 1, the other macro are not called.
--
Gary''s Student - gsnu200737


"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

Private Sub Workbook_Open()
Const mainWorkbookName = "Book1"

If ThisWorkbook.Name < mainWorkbookName & ".xls" Then
MsgBox "Don't run macros..."
End If
End Sub

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

Mike, I thought about that. But need more input from the OP -- it sounds
like they may not be creating from a .xlt template file all of the time, but
may be working with a .xls that they use those macros to clean out as if it
were a template.

"Mike" wrote:

Private Sub Workbook_Open()
Const mainWorkbookName = "Book1"

If ThisWorkbook.Name < mainWorkbookName & ".xls" Then
MsgBox "Don't run macros..."
End If
End Sub

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

JLatham

One of my colleagues has tried my spreadsheet with the new 'snapshot' code,
but there are a few issues.

1) If No is selected from the snapshot prompt, the Save As dialog still
appears. Cancel is selected and cell Z100 returns to being 'locked'.

2) If Yes is selected from the snapshot prompt, the Save As dialog appears,
which is fine because that is what would be expected. If Cancel is selected,
the cell Z100 contains the value "Snapshot" - not really the best outcome as
not saved completely.

I'm wondering whether to have a command button, labelled 'Take Snapshot',
instead of a command button - the main Save button would be disabled (the doc
is read-only anyway, so if they want to save, they can use this button).
This button should simply invoke 'Save As' functionality and it is only on
selecting OK to confirm the save that cell Z100 states 'snapshot'. If Save
is cancelled, Z100 should remain clear (and unlocked).

Macro's should not run in a document where Z100 states "Snapshot". The
'Take Snapshot' button should be disabled and the main Save button should be
re-enabled.

My VB knowledge is limited, so thank you for your help so far and I hope you
can help further with this.

"JLatham" wrote:

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

Don't despair - I'm working on it.

"Sarah (OGI)" wrote:

JLatham

One of my colleagues has tried my spreadsheet with the new 'snapshot' code,
but there are a few issues.

1) If No is selected from the snapshot prompt, the Save As dialog still
appears. Cancel is selected and cell Z100 returns to being 'locked'.

2) If Yes is selected from the snapshot prompt, the Save As dialog appears,
which is fine because that is what would be expected. If Cancel is selected,
the cell Z100 contains the value "Snapshot" - not really the best outcome as
not saved completely.

I'm wondering whether to have a command button, labelled 'Take Snapshot',
instead of a command button - the main Save button would be disabled (the doc
is read-only anyway, so if they want to save, they can use this button).
This button should simply invoke 'Save As' functionality and it is only on
selecting OK to confirm the save that cell Z100 states 'snapshot'. If Save
is cancelled, Z100 should remain clear (and unlocked).

Macro's should not run in a document where Z100 states "Snapshot". The
'Take Snapshot' button should be disabled and the main Save button should be
re-enabled.

My VB knowledge is limited, so thank you for your help so far and I hope you
can help further with this.

"JLatham" wrote:

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

Sarah, This gets a bit involved, as I'm sure you're aware - you have to have
some code in the Workbook event handling module, and other code in standard
module(s).

What I've done is create a workbook that you can download that either
provides a solution you can incorporate into your current workbook or use as
a model to do your own customization.

To download the file click this link and choose Save to your local drive:
http://www.jlathamsite.com/uploads/ControlledSaves.xls

What it does:
While the workbook is open/active it will disable the File | Save, File |
Save As, File | Close menu items and also disable the file save icon in the
standard toolbar. It adds a custom menu entry just to the left of the [Help]
menu entry in the workbook menu with 2 options: Close (without save - to use
when you want to close and delete / 'lose' all entries the user has added).
Save As Snapshot - which will do that, and if user cancel's the Save As
operation, it backs off completely, leaving the workbook just as it was. But
if they go thru with the operation, then it has menu items changed: custom
menu then only has "save without changes" available.

To open the workbook for maintenance and use 'normally' during that time,
you can choose [Disable] macros when you open it, then the custom menu
doesn't get created and the regular menu items act normally.

The code should show you how to work with the various built-in menus and
create or add to the custom menu item that is in it. My email address is in
the main code module, along with this link:
http://support.microsoft.com/default...&\1Product=xlw
which pretty much tells everything you ever wanted to know about working
with the menus and toolbars in pre-2007 Excel.


"Sarah (OGI)" wrote:

JLatham

One of my colleagues has tried my spreadsheet with the new 'snapshot' code,
but there are a few issues.

1) If No is selected from the snapshot prompt, the Save As dialog still
appears. Cancel is selected and cell Z100 returns to being 'locked'.

2) If Yes is selected from the snapshot prompt, the Save As dialog appears,
which is fine because that is what would be expected. If Cancel is selected,
the cell Z100 contains the value "Snapshot" - not really the best outcome as
not saved completely.

I'm wondering whether to have a command button, labelled 'Take Snapshot',
instead of a command button - the main Save button would be disabled (the doc
is read-only anyway, so if they want to save, they can use this button).
This button should simply invoke 'Save As' functionality and it is only on
selecting OK to confirm the save that cell Z100 states 'snapshot'. If Save
is cancelled, Z100 should remain clear (and unlocked).

Macro's should not run in a document where Z100 states "Snapshot". The
'Take Snapshot' button should be disabled and the main Save button should be
re-enabled.

My VB knowledge is limited, so thank you for your help so far and I hope you
can help further with this.

"JLatham" wrote:

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

JLatham

Thank you ever so much. This works a treat! I have changed some of the
code slightly, i.e. to unprotect/unlock/re-protect the relevant
cells/worksheets and also to prompt the user with an 'are you sure you wish
to close without saving' dialog. This has been a useful exercise and I can't
thank you enough for your time and assistance.

Cheers!!

"JLatham" wrote:

Sarah, This gets a bit involved, as I'm sure you're aware - you have to have
some code in the Workbook event handling module, and other code in standard
module(s).

What I've done is create a workbook that you can download that either
provides a solution you can incorporate into your current workbook or use as
a model to do your own customization.

To download the file click this link and choose Save to your local drive:
http://www.jlathamsite.com/uploads/ControlledSaves.xls

What it does:
While the workbook is open/active it will disable the File | Save, File |
Save As, File | Close menu items and also disable the file save icon in the
standard toolbar. It adds a custom menu entry just to the left of the [Help]
menu entry in the workbook menu with 2 options: Close (without save - to use
when you want to close and delete / 'lose' all entries the user has added).
Save As Snapshot - which will do that, and if user cancel's the Save As
operation, it backs off completely, leaving the workbook just as it was. But
if they go thru with the operation, then it has menu items changed: custom
menu then only has "save without changes" available.

To open the workbook for maintenance and use 'normally' during that time,
you can choose [Disable] macros when you open it, then the custom menu
doesn't get created and the regular menu items act normally.

The code should show you how to work with the various built-in menus and
create or add to the custom menu item that is in it. My email address is in
the main code module, along with this link:
http://support.microsoft.com/default...&\1Product=xlw
which pretty much tells everything you ever wanted to know about working
with the menus and toolbars in pre-2007 Excel.


"Sarah (OGI)" wrote:

JLatham

One of my colleagues has tried my spreadsheet with the new 'snapshot' code,
but there are a few issues.

1) If No is selected from the snapshot prompt, the Save As dialog still
appears. Cancel is selected and cell Z100 returns to being 'locked'.

2) If Yes is selected from the snapshot prompt, the Save As dialog appears,
which is fine because that is what would be expected. If Cancel is selected,
the cell Z100 contains the value "Snapshot" - not really the best outcome as
not saved completely.

I'm wondering whether to have a command button, labelled 'Take Snapshot',
instead of a command button - the main Save button would be disabled (the doc
is read-only anyway, so if they want to save, they can use this button).
This button should simply invoke 'Save As' functionality and it is only on
selecting OK to confirm the save that cell Z100 states 'snapshot'. If Save
is cancelled, Z100 should remain clear (and unlocked).

Macro's should not run in a document where Z100 states "Snapshot". The
'Take Snapshot' button should be disabled and the main Save button should be
re-enabled.

My VB knowledge is limited, so thank you for your help so far and I hope you
can help further with this.

"JLatham" wrote:

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.

I figured you'd need to do some changes such as the unprotect/protect type
additions, along with the macros you already had in place. I figured my
major role in this was helping you get the use of Save/Save As and Close
under control. I had a bit of help in all of that myself, some coding
examples from other people and sites was invaluable to me in it:
Ron deBruin's site, Tushar Mehta's site and tek-tips.com along with the
previously provided link to the MSFT page all played a significant role in
putting it together.

"Sarah (OGI)" wrote:

JLatham

Thank you ever so much. This works a treat! I have changed some of the
code slightly, i.e. to unprotect/unlock/re-protect the relevant
cells/worksheets and also to prompt the user with an 'are you sure you wish
to close without saving' dialog. This has been a useful exercise and I can't
thank you enough for your time and assistance.

Cheers!!

"JLatham" wrote:

Sarah, This gets a bit involved, as I'm sure you're aware - you have to have
some code in the Workbook event handling module, and other code in standard
module(s).

What I've done is create a workbook that you can download that either
provides a solution you can incorporate into your current workbook or use as
a model to do your own customization.

To download the file click this link and choose Save to your local drive:
http://www.jlathamsite.com/uploads/ControlledSaves.xls

What it does:
While the workbook is open/active it will disable the File | Save, File |
Save As, File | Close menu items and also disable the file save icon in the
standard toolbar. It adds a custom menu entry just to the left of the [Help]
menu entry in the workbook menu with 2 options: Close (without save - to use
when you want to close and delete / 'lose' all entries the user has added).
Save As Snapshot - which will do that, and if user cancel's the Save As
operation, it backs off completely, leaving the workbook just as it was. But
if they go thru with the operation, then it has menu items changed: custom
menu then only has "save without changes" available.

To open the workbook for maintenance and use 'normally' during that time,
you can choose [Disable] macros when you open it, then the custom menu
doesn't get created and the regular menu items act normally.

The code should show you how to work with the various built-in menus and
create or add to the custom menu item that is in it. My email address is in
the main code module, along with this link:
http://support.microsoft.com/default...&\1Product=xlw
which pretty much tells everything you ever wanted to know about working
with the menus and toolbars in pre-2007 Excel.


"Sarah (OGI)" wrote:

JLatham

One of my colleagues has tried my spreadsheet with the new 'snapshot' code,
but there are a few issues.

1) If No is selected from the snapshot prompt, the Save As dialog still
appears. Cancel is selected and cell Z100 returns to being 'locked'.

2) If Yes is selected from the snapshot prompt, the Save As dialog appears,
which is fine because that is what would be expected. If Cancel is selected,
the cell Z100 contains the value "Snapshot" - not really the best outcome as
not saved completely.

I'm wondering whether to have a command button, labelled 'Take Snapshot',
instead of a command button - the main Save button would be disabled (the doc
is read-only anyway, so if they want to save, they can use this button).
This button should simply invoke 'Save As' functionality and it is only on
selecting OK to confirm the save that cell Z100 states 'snapshot'. If Save
is cancelled, Z100 should remain clear (and unlocked).

Macro's should not run in a document where Z100 states "Snapshot". The
'Take Snapshot' button should be disabled and the main Save button should be
re-enabled.

My VB knowledge is limited, so thank you for your help so far and I hope you
can help further with this.

"JLatham" wrote:

I think you've done what I would have recommended: unprotect the sheet in the
code, change the value, reprotect.

By unlocking that cell, you've afforded them the opportunity to change the
file out of snapshot status by simply deleting that entry in the sheet.
Whether or not that's a good thing is a decision to be made by you.

There's really no way around the initial prompt about enable/disable macros
when the file contains code, even if it isn't going to be executed. The mere
presence of a code module will trigger that. Try this: create a new
workbook. Go into the VB Editor and insert a module. Don't put any code in
it. Save and close the workbook and then reopen it - you'll get the prompt
even though there's really not code to be executed - just the bare module in
the workbook.

"Sarah (OGI)" wrote:

I think I may have found a way around the first issue. I disabled the
macro's on entry, unprotected the worksheet, made the relevant cell unlocked,
re-protected it and saved. On revisiting this document, enabling the
macro's, the relevant cell remains unlocked and the macro to populate the
cell with the word "snapshot" can work glitch-free.



"Sarah (OGI)" wrote:

JLatham

Thank you for your help on this - it's working fine except for 2 things:

Firstly, I entered the BeforeSave code, forgetting that I'd protected the
worksheet and did not 'unlock' the cell into which the word "Snapshot" will
appear. I amended the worksheet so that this cell became unlocked and
therefore could contain the word "Snapshot". However, I can't then save the
document with these new changes - if I were to save with the same name, as a
snapshot, this 'master' doc will always be set as a snapshot? If I say No,
the document is saved, but the newly unlocked cell becomes locked again. Is
there a way around this, other than leaving the worksheet unprotected?

Secondly, when it works for a document with a new name, and as a snapshot of
the details, the user is prompted with the disable/enable macro's dialog.
The buttons neither disable or enable the macro's on entry, but is there a
way to avoid this prompt?

Thanks

"JLatham" wrote:

There are several ways to go about this, but I think this is a relatively
easy one, use a 'control' cell somewhere on one of your sheets to indicate
whether or not it is to be or previously was saved as a "snapshot in time"
type that's not to have data deleted with the code. This would all go in
your Workbook code module, which I presume you know how to get to since you
mention the on Open code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
If MsgBox("Is this to be a permanent Snapshot copy of the file?", _
vbYesNo, "Type Save") = vbYes Then
'put something somewhere that you can test later to prevent
'execution of macros
'We will put the word "Snapshot" into cell L1 on Sheet1 for this
Worksheet("Sheet1").Range("L1") = "Snapshot"
Else
'not a snapshot, make sure test location is empty
Worksheet("Sheet1").Range("L1").Clear
End If
End If
End Sub

Private Sub Workbook_Open()
If IsEmpty(Worksheets("Sheet1").Range("L1")) Then
'go ahead and perform your clearing operations
'within this area
End If
'use same IsEmpty() test in any other
'macros you don't want to run while in
'a 'snapshot in time' type workbook
End Sub

Notice in the first routine [ _BeforeSave()] that it even tests to see if
this is already a 'snapshot' book, and if it is, doesn't even bother with the
prompt, but just leaves the text in the control cell and moves on, completing
the save without interrupting with the prompt.

"Sarah (OGI)" wrote:

I have a workbook with 3 worksheets. There are various macro's set up which
will clear various cells, either on Open or via command buttons.
The idea is that colleagues will open the master document, enable macro's on
entry and the use the workbook as required. If they don't save the details
entered, they can close this document, but on revisiting it, all previous
details will be lost. Should they wish to save the details entered - which
is being recommended so they can review the details at a later date, they
need to save as a new document.
However, this new document still prompts the user to disable/enable macro's.
Is there any way of automatically disabling the macro's on subsequent
documents, which have been saved from the original?
Thanks in advance.