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.

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.

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