|
How could I default an excel file to be openned as READ ONLY once it is saved under a name?
Ladies and Gentlemen:
I have a question and I need your help. The question can be explained in the following sequence: a): I have an original file 1 which is read only; b): Users open up my original file, modify it, save it as file 2 and CLOSE it. Then whenever file 2 is re-openned, it should be openned as a read only file; c): Users open up file 2, modify it, save it as file 3 and CLOSE it. Then whenever file 3 is re-openned, it should be openned as a read only file;and so on! In summary, I do NOT want users to over-write the existing saved files. The only thing they can do is to open up any file they want, modify it, save it as a different file. Once this is done and the file is closed, they can not come back and modify it anymore unless they save it under another different name! Thank you so much for your help! George |
How could I default an excel file to be openned as READ ONLY once it is saved under a name?
George,
Try this in the ThisWorkbook module... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo BadSave Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show Cancel = True BadSave: Application.EnableEvents = True End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "George" wrote in message Ladies and Gentlemen: I have a question and I need your help. The question can be explained in the following sequence: a): I have an original file 1 which is read only; b): Users open up my original file, modify it, save it as file 2 and CLOSE it. Then whenever file 2 is re-openned, it should be openned as a read only file; c): Users open up file 2, modify it, save it as file 3 and CLOSE it. Then whenever file 3 is re-openned, it should be openned as a read only file;and so on! In summary, I do NOT want users to over-write the existing saved files. The only thing they can do is to open up any file they want, modify it, save it as a different file. Once this is done and the file is closed, they can not come back and modify it anymore unless they save it under another different name! Thank you so much for your help! George |
How could I default an excel file to be openned as READ ONLY once it is saved under a name?
Hi, Jim:
Thank you so much for all your time and efforts. It looks very good. However, we need one more step to get it done. That is, whenever I am trying to save the file, it will ask me "The file already exists. Do you want to replace the existing file?". If I choose "No", everything is perfect. But I DO NOT like people to choose "Yes". In other words, I DO NOT want people to replace any exisiting file. Whenever they modify a file, they MUST save it under a different name. There is no exceptions. Thanks again, George Jim Cone wrote: George, Try this in the ThisWorkbook module... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo BadSave Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show Cancel = True BadSave: Application.EnableEvents = True End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "George" wrote in message Ladies and Gentlemen: I have a question and I need your help. The question can be explained in the following sequence: a): I have an original file 1 which is read only; b): Users open up my original file, modify it, save it as file 2 and CLOSE it. Then whenever file 2 is re-openned, it should be openned as a read only file; c): Users open up file 2, modify it, save it as file 3 and CLOSE it. Then whenever file 3 is re-openned, it should be openned as a read only file;and so on! In summary, I do NOT want users to over-write the existing saved files. The only thing they can do is to open up any file they want, modify it, save it as a different file. Once this is done and the file is closed, they can not come back and modify it anymore unless they save it under another different name! Thank you so much for your help! George |
How could I default an excel file to be openned as READ ONLY once it is saved under a name?
George,
Well, try this version. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo BadSave Dim strCurrentName As String Dim strNewName As Variant Dim lngPos As Long strCurrentName = Me.Name StartOver: strNewName = Application.GetSaveAsFilename(, "Workbook (*.xls),*.xls") If strNewName = False Then Cancel = True Exit Sub Else lngPos = InStrRev(strNewName, Application.PathSeparator, -1, vbTextCompare) strNewName = Mid$(strNewName, lngPos + 1, 999) If StrComp(strNewName, strCurrentName, vbTextCompare) < 0 Then Application.EnableEvents = False Me.SaveAs strNewName, xlNormal Application.EnableEvents = True Cancel = True Else MsgBox "Please use a new name for the file. ", vbExclamation, "Save As" GoTo StartOver End If End If Exit Sub BadSave: Beep Cancel = True Application.EnableEvents = True End Sub '--------------- "George" wrote in message Hi, Jim: Thank you so much for all your time and efforts. It looks very good. However, we need one more step to get it done. That is, whenever I am trying to save the file, it will ask me "The file already exists. Do you want to replace the existing file?". If I choose "No", everything is perfect. But I DO NOT like people to choose "Yes". In other words, I DO NOT want people to replace any exisiting file. Whenever they modify a file, they MUST save it under a different name. There is no exceptions. Thanks again, George |
How could I default an excel file to be openned as READ ONLY once it is saved under a name?
George,
You are welcome. Jim Cone "George" wrote in message oups.com... Dear Jim: It is perfect. Whenever you stop by Houston, please let me know and I will buy you a meal. My e-mail is . Thanks again and have a great day! George Jim Cone wrote: George, Well, try this version. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo BadSave Dim strCurrentName As String Dim strNewName As Variant Dim lngPos As Long strCurrentName = Me.Name StartOver: strNewName = Application.GetSaveAsFilename(, "Workbook (*.xls),*.xls") If strNewName = False Then Cancel = True Exit Sub Else lngPos = InStrRev(strNewName, Application.PathSeparator, -1, vbTextCompare) strNewName = Mid$(strNewName, lngPos + 1, 999) If StrComp(strNewName, strCurrentName, vbTextCompare) < 0 Then Application.EnableEvents = False Me.SaveAs strNewName, xlNormal Application.EnableEvents = True Cancel = True Else MsgBox "Please use a new name for the file. ", vbExclamation, "Save As" GoTo StartOver End If End If Exit Sub BadSave: Beep Cancel = True Application.EnableEvents = True End Sub '--------------- "George" wrote in message Hi, Jim: Thank you so much for all your time and efforts. It looks very good. However, we need one more step to get it done. That is, whenever I am trying to save the file, it will ask me "The file already exists. Do you want to replace the existing file?". If I choose "No", everything is perfect. But I DO NOT like people to choose "Yes". In other words, I DO NOT want people to replace any exisiting file. Whenever they modify a file, they MUST save it under a different name. There is no exceptions. Thanks again, George |
| All times are GMT +1. The time now is 02:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com