Workbook_BeforeSave - Prevent use of "x" filename on save or save
 

I have a workbook (metric chart preview.xls) containing several charts.
Source data for these charts is pushed into the workbook from an Access
Database. Although users will want to manipulate, change, and or modify the
charts within this workbook, any permanent change will break the feed from
the database. Therefore, users must be able to open, manipulate and then save
their own version of the workbook without affecting the original. My initial
thought was to simply set the workbook to read-only to force users into using
Save As and a different File name. This works great to prevent users from
saving changes to the file, however also prevents data update form the Access
Database. My second thought was to disable the Save option and prompt users
to save a copy of the file using Save As with the following
Workbook_BeforeSave code I found while searching this forum,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
MsgBox "Please choose Save As and Rename this file to save your own
version.", vbInformation
Cancel = True
End If
End Sub


This works well enough except users still have the opportunity to save over
the original file. The directory may change depending on user, however, the
original file will always remain as the same. I think what I am looking for
is the ability to prevent the use of "metric chart preview.xls" as a file
name when using the Save or Save As dialogue box. Is there a way to do this?
Thanks, Mike

Workbook_BeforeSave - Prevent use of "x" filename on save or save
 

Using that Workbook_beforesave event you've got below, you'd want something
more like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
if thisworkbook.name = "metric chart preview.xls" then
cancel = true
msgbox "Warning - name not allowed"
end if
end sub

except that you might need to add a flag in your workbook (and check it in
the "if thisworkbook"... statement) to see if you're supposed to be saving
over (as I imagine in the case of when Access updates it)

HTH,

Mike.


"Mike G - D.C." wrote in message
...
I have a workbook (metric chart preview.xls) containing several charts.
Source data for these charts is pushed into the workbook from an Access
Database. Although users will want to manipulate, change, and or modify
the
charts within this workbook, any permanent change will break the feed from
the database. Therefore, users must be able to open, manipulate and then
save
their own version of the workbook without affecting the original. My
initial
thought was to simply set the workbook to read-only to force users into
using
Save As and a different File name. This works great to prevent users from
saving changes to the file, however also prevents data update form the
Access
Database. My second thought was to disable the Save option and prompt
users
to save a copy of the file using Save As with the following
Workbook_BeforeSave code I found while searching this forum,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
MsgBox "Please choose Save As and Rename this file to save your own
version.", vbInformation
Cancel = True
End If
End Sub


This works well enough except users still have the opportunity to save
over
the original file. The directory may change depending on user, however,
the
original file will always remain as the same. I think what I am looking
for
is the ability to prevent the use of "metric chart preview.xls" as a file
name when using the Save or Save As dialogue box. Is there a way to do
this?
Thanks, Mike

Workbook_BeforeSave - Prevent use of "x" filename on save or s
 

I really appreciate the quick response and the code provided works great for
what I initially asked for. In execution of the code though, I've realized
that users will need to save their own version of the original file using the
Save As option. Is there a way to amend the code so that the Save As Dialogue
box is available for use, but users cant use/select the file name, metric
chart preview.xls if it already exists within the save location?

Hope I didn't wear out my welcome, thanks Mike


"Mike" wrote:

Using that Workbook_beforesave event you've got below, you'd want something
more like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
if thisworkbook.name = "metric chart preview.xls" then
cancel = true
msgbox "Warning - name not allowed"
end if
end sub

except that you might need to add a flag in your workbook (and check it in
the "if thisworkbook"... statement) to see if you're supposed to be saving
over (as I imagine in the case of when Access updates it)

HTH,

Mike.


"Mike G - D.C." wrote in message
...
I have a workbook (metric chart preview.xls) containing several charts.
Source data for these charts is pushed into the workbook from an Access
Database. Although users will want to manipulate, change, and or modify
the
charts within this workbook, any permanent change will break the feed from
the database. Therefore, users must be able to open, manipulate and then
save
their own version of the workbook without affecting the original. My
initial
thought was to simply set the workbook to read-only to force users into
using
Save As and a different File name. This works great to prevent users from
saving changes to the file, however also prevents data update form the
Access
Database. My second thought was to disable the Save option and prompt
users
to save a copy of the file using Save As with the following
Workbook_BeforeSave code I found while searching this forum,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
MsgBox "Please choose Save As and Rename this file to save your own
version.", vbInformation
Cancel = True
End If
End Sub


This works well enough except users still have the opportunity to save
over
the original file. The directory may change depending on user, however,
the
original file will always remain as the same. I think what I am looking
for
is the ability to prevent the use of "metric chart preview.xls" as a file
name when using the Save or Save As dialogue box. Is there a way to do
this?
Thanks, Mike

Workbook_BeforeSave - Prevent use of "x" filename on save or s
 

Mike, try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strName As String
If (SaveAsUI = True) Or (ThisWorkbook.Name = "metric chart preview.xls")
Then
Do
strName = Application.GetSaveAsFilename
Loop Until Right(strName, Len(strName) - InStrRev(strName, "\")) _
< "metric chart preview.xls"
Application.EnableEvents = False
If UCase(Left(strName, 5)) < "FALSE" Then
ThisWorkbook.SaveAs strName
End If
Cancel = True
Application.EnableEvents = True
End If
End Sub

You might want to put in a notification or something to let the user know
why they're being asked repeatedly for a name if they're trying to use your
reserved filename.


Mike.


"Mike G - D.C." wrote in message
...
I really appreciate the quick response and the code provided works great
for
what I initially asked for. In execution of the code though, I've realized
that users will need to save their own version of the original file using
the
Save As option. Is there a way to amend the code so that the Save As
Dialogue
box is available for use, but users can't use/select the file name,
metric
chart preview.xls if it already exists within the save location?

Hope I didn't wear out my welcome, thanks Mike


"Mike" wrote:

Using that Workbook_beforesave event you've got below, you'd want
something
more like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
if thisworkbook.name = "metric chart preview.xls" then
cancel = true
msgbox "Warning - name not allowed"
end if
end sub

except that you might need to add a flag in your workbook (and check it
in
the "if thisworkbook"... statement) to see if you're supposed to be
saving
over (as I imagine in the case of when Access updates it)

HTH,

Mike.


"Mike G - D.C." wrote in message
...
I have a workbook (metric chart preview.xls) containing several
charts.
Source data for these charts is pushed into the workbook from an
Access
Database. Although users will want to manipulate, change, and or
modify
the
charts within this workbook, any permanent change will break the feed
from
the database. Therefore, users must be able to open, manipulate and
then
save
their own version of the workbook without affecting the original. My
initial
thought was to simply set the workbook to read-only to force users
into
using
Save As and a different File name. This works great to prevent users
from
saving changes to the file, however also prevents data update form the
Access
Database. My second thought was to disable the Save option and prompt
users
to save a copy of the file using Save As with the following
Workbook_BeforeSave code I found while searching this forum,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
MsgBox "Please choose Save As and Rename this file to save your own
version.", vbInformation
Cancel = True
End If
End Sub


This works well enough except users still have the opportunity to save
over
the original file. The directory may change depending on user,
however,
the
original file will always remain as the same. I think what I am
looking
for
is the ability to prevent the use of "metric chart preview.xls" as a
file
name when using the Save or Save As dialogue box. Is there a way to
do
this?
Thanks, Mike