Excel - Allows SaveAs Only

Quote:

---

Originally Posted by RobDog888

I would add more code to prevent it from being saved as the same file name but they could still rename the file and save again to bypass it.

---

I'm not concerned about that. First of all, the type of customers I have for this probably don't know how to do that, and second if they did, then so be it. They are screwing themselves. You can make something idiot proof, but you can't make it moron proof.

I don't want to disable any buttons. I want to alter the functionality. I want Save to automatically act as though Save As was selected, and I do not want Excel prompting the users to save their work. If they exit without doing a Save As they will lose what they did so they will have to do it all over again. Hopefully they will be bright enough to only do that once.

Quote:

---

Originally Posted by RobDog888

If you do something like this it will prevent them from saving it as the original file name. Granted they can rename the original file but how will they know that is the workaround :D

Code:

---

Option Explicit
'Change the filename comparison to reflect your filename your sending the workbook out as.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True And ThisWorkbook.Name <> "Book1.xls" Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

---

Using this code, even when I click File/Save As, I get the message box indicating I should use Save As.

@Hack, try this code, it may help (I haven't fully test it.) No need to disable anything.

Please note that if user Disable Macro or get into Design Mode then he still can Overwrite the Template.

It's better save the file as Excel Template file type (.xlt) it will have less accidents.

On design the template, save it to another filename then rename the file to what you specify in 'OriginalTemplate' in the code.

Code:

---

'-- In ThisWorkbook module
Option Explicit

Const OriginalTemplate = "C:\Users\Hack\VBForums\MyTemplate.xlt"
Dim SaveByCode As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If Me.FullName = OriginalTemplate Then
      If Me.Saved = False Then
        If ForceSaveAs() = "False" Then
            Saved = True '-- trick the Workbook, let it thinks already saved.
        End If
      End If
  End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim NewFullName As String
 
  If SaveByCode = True Then
      SaveByCode = False
  ElseIf Me.FullName = OriginalTemplate Then
      If SaveAsUI = False Then
        ForceSaveAs
        Cancel = True
      ElseIf NewFullName = OriginalTemplate Then
        MsgBox "Please choose another file name", vbExclamation, _
                "Cannot Overwrite Template File"
      End If
  End If
End Sub

Private Function ForceSaveAs() As String
  Dim NewFullName As String
  Do
      NewFullName = Application.GetSaveAsFilename
      If NewFullName <> OriginalTemplate Then Exit Do
      MsgBox "Please choose another file name", vbExclamation, _
            "Cannot Overwrite Template File"
  Loop
  If NewFullName <> "False" Then
      SaveByCode = True
      On Error Resume Next
      Me.SaveAs NewFullName
      If Err <> 0 Then NewFullName = "False"
      SaveByCode = False
  End If
  ForceSaveAs = NewFullName
End Function

---

I assume the ForceSaveAs function would go in the Workbook not the Worksheet right?

Two things:

I like the fact that if you hit File/Save, the Save As dialog appears. That is good.

However, if I hit the Red X in the upper right hand corner, I'm still prompted with Excel's message asking me if I want to save my changes. If I click Yes, it just saves it, i.e., it overwrites the existing file with my new changes, which, of course, I don't want. How to I get a Save As dialog to appear in this scenerio?

Thats similar to my code suggestion to compare the file name as to know if they are trying to save over the original file name or not.

Should work. :)

I wrote the code in Post#42 at midnight (12:36 AM).

You can change _BeforeClose() to:

Code:

---

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim Rep As VbMsgBoxResult

  If Me.FullName = OriginalTemplate Then
      If Me.Saved = False Then
        Rep = MsgBox("Do you want to save the changes you made to workbook " _
                      & Me.Name & "?", vbExclamation + vbYesNoCancel)
        If Rep = vbCancel Then
            Cancel = True
        ElseIf Rep = vbNo Then
            Saved = True '-- trick the Workbook, let it thinks already saved.
        ElseIf ForceSaveAs() = "False" Then
            Cancel = True
        End If
      End If
  End If
End Sub

---

and one small change in ForceSaveAs()

Code:

---

Private Function ForceSaveAs() As String
  Dim NewFullName As String
  Do
      NewFullName = Application.GetSaveAsFilename("")
      ... ...

---

I will give another approach in next post that will be much simpler.

This is another approach, that is much simpler and much easier:

On Open the workbook will check if its fullname is the OriginalTemplate name then turn ReadOnly flag on, otherwise allow ReadWrite as normal.

I repeat the suggestion in the other post: It will be better to save your template with an Excel Template file type (.XLT), however this is not required.

Do not combine with the code from the other post.

Code:

---

'-- In ThisWorkbook module
Option Explicit

Private Sub Workbook_Open()
  Const OriginalTemplate = "C:\!Tools\MyTemplate.xlt"
 
  On Error Resume Next
  If Me.FullName = OriginalTemplate Then
      Me.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
  Else
      '-- error may happen here so resume next
      Me.ChangeFileAccess Mode:=xlReadWrite
  End If
  Saved = True
End Sub

---

:) Haha! :) I hope I can get 50 reputation points from this post.

i might be missing something here, but why don't you save your workbook as a workbook template (.xlt),
all the code and data will be included in any workbook opened based on that template, the user just open a new workbook each time, but no changes will be made to the template no matter what they do as far as saving the workbook

pipped again........... lol

@pete, although I suggested the template should be saved with file type *.xlt, but even that it is not always the case. Users may be able to open the template file the same way as the developer to open it.
(Instead of double click on an .xlt file that will create a new file in the memory space without extension based on the template, users can use Open button or menu to select an .xlt and open it directly.)

It is an .xls file, and a .xls file it must stay (don't ask)

So, all solutions to this little problem I have to do in code.

Bottom line Hack is that in the save event you need to verify if its the original workbook filename as I suggested and if it is disallow the save. If they do a save as it will no longer be opened as your original workbook so future changes/saves will not overwrite the original.

Use the simple code in Post#47, Excel will take care of everything when the template becomes ReadOnly.

If you are not sure where you will put the template, you can verify on workbook's Name only instead of Full Name:

Code:

---

Private Sub Workbook_Open()
  Const TemplateName = "MyTemplate.xlt"
 
  On Error Resume Next
  If Me.Name = TemplateName Then
      Me.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
  Else
      '-- error may happen here so resume next
      Me.ChangeFileAccess Mode:=xlReadWrite
  End If
  Saved = True
End Sub

---

If the original template has no code and you don't want to write any code in it:
You can save the original template with ReadOnly option that comes along with a write access Password.
On openning, users will be prompted with a message:

http://www.vbforums.com/attachment.p...1&d=1201211169

Any method above is not safe if user has a security access of Write or Modify to the folder or the template file, they can open other file then on Save As they can overwrite your template.

The best way is to apply system Security option on the file or the folder that is on a network drive, make it become ReadOnly.
Do not use ReadOnly option on General tab of file Properties that can be turned on or off by anyone.

I can't use an .XLT file - only an .XLS file

I recommend you to use Security option on the file or folder, I use this option for hundreds Excel files that I create weekly and put on several network folders.
It doesn't matter what extension you use.

I dont think that may apply here as its probably a distributed slx file?

Quote:

---

Originally Posted by RobDog888

I dont think that may apply here as its probably a distributed slx file?

---

Actually, it is a single spreadsheet that is used by mutliple people (it is set up as a Shared Workbook) that is house on a network drive.