CODEWITHSUNDEEP
excelvba
Mike
Mike

Reputation: 4405

Force Save As, MS Excel VBA

I'm a bit of a newby with VBA in MS Office products. I've been searching for some code that will force a user to "Save As' a .xls file I use as a template (but it's not an actual template file type)

Basically, I have this:

  1. User opens .xls, enters some data into some field and then File-->Save As to their own location
  2. Sometimes user clicks save instead, therefore overwriting the .xls which I don't want to happen.

I've been looking into some ideas, but I'm not sure how to implement it the best. I think a prompt when the user first opens the .xls to save to a new location is the best approach, but thinking forward, if they have already saved the file to a new location and decide to edit the new one, I want them to be able to 'Save' at that point because now it is their own file, not the original.

If someone can point me in the right direction or find flaws in my logic, I'd love to hear it.

Thanks, Mike

Upvotes: 4

Views: 31875

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

I agree with the others that templates and read-only are better options, but if you're set on rolling your own, here's an example to get you started. It goes in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim sNewName As String

    Cancel = True 'Cancel the save operation

    sNewName = Application.GetSaveAsFilename(Replace(Me.Name, ".xls", "1.xls"))

    If sNewName <> Me.FullName And sNewName <> "False" Then
        Application.EnableEvents = False
            Me.SaveAs sNewName
        Application.EnableEvents = True
    End If

End Sub

Upvotes: 4

Siddharth Rout
Siddharth Rout

Reputation: 149287

I use as a template (but it's not an actual template file type)

The most simplest way is to save the file with Read-Only Recommended set to true. See the snapshot

enter image description here

That ways even if the user tries to do a Save, Excel will automatically prompt for a Save As

enter image description here

HTH

Upvotes: 6

Related Questions