Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
In Excel 2003 is there a way to prevent "Save As" and "Print"? | Excel Discussion (Misc queries) | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Automatically include contents of a cell in "Save As..." filename. | Excel Programming |