Allow save as but not save

[Declamatory]

I have a workbook that I want people to be able to access and run a macro that changes the data in the workbook and then does a save as. I don't want people to be able to save over the original workbook. Does anyone know how to do this?

 

[Joe4]

Make the workbook Read Only.

Read-only Excel Workbook

This example shows you how to make your Excel workbook read-only. Users without a password can still save changes by creating a new copy of the workbook.

www.excel-easy.com

 

[Radoslaw Poprawski]

you would have to use something like this:

Option Explicit

Sub SaveFile()

Dim fdate As Date
Dim fname As String
Dim path As String

fdate = Range("A1").Value
path = Application.ActiveWorkbook.path

If fdate > 0 Then
    fname = "Event " & fdate
    Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Else
    MsgBox "Chose a date for the event", vbOKOnly
End If

End Sub

This needsto beadjusted to your needs, but without more details its hard to do it.

 

[Joe4]

you would have to use something like this:

Option Explicit

Sub SaveFile()

Dim fdate As Date
Dim fname As String
Dim path As String

fdate = Range("A1").Value
path = Application.ActiveWorkbook.path

If fdate > 0 Then
    fname = "Event " & fdate
    Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Else
    MsgBox "Chose a date for the event", vbOKOnly
End If

End Sub

This needsto beadjusted to your needs, but without more details its hard to do it.

That code will do a "SaveAs", but does not prevent the user from doing their own "Save" saving over the original file, which is a vital requirement of the question.

 

[Declamatory]

Also, sorry to do an add on but I will need to occasionally edit the workbook e.g. public holidays or new products so I need to be able to go in and save.

 

[Joe4]

Also, sorry to do an add on but I will need to occasionally edit the workbook e.g. public holidays or new products so I need to be able to go in and save.

Did you look at the link I provided?
You will be able to open, edit, and save it, provided you know the password you set on it to make it read-only.
So only those who you want to have the authority to make changes to and save over the file should know the password.