Deny Users to save over existing form, but enable Save As option

[I heart Excel]

I have created a form within Excel 2007 for our end users to fill out and submit through a approval trail. I have set up everything to go, the only thing I am really struggling with are the saving options.

I want an end user to be able to access the form, fill out the appropriate fields and submit the form. I then want them to, if required, to save the form in a personal location.

Therefore I need to stop 'Save' but allow 'Save As'. I have read a number of forums and tried different things, but once saved the end users can still override the form.

Any ideas?!

 

[VoG]

Try this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
    Cancel = True
    MsgBox "You must use Save As", vbExclamation
End If
End Sub

 

[Michael Simpson]

A simple question. If you allow Save as, what is to stop them from drilling down to wherever the workbook was originally opened and saving it with the same name ?

 

[I heart Excel]

I did the following -

Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
Cancel = True
MsgBox "You must use Save As", vbExclamation
End If
End Sub

The top one is already on the form.

It hasn't worked as one of my colleagues has bee nable to fill out the fields and save over the existing document.

Could I be doing something wrong?

*Also with the Save As over the original document they wouldn't do this, as they would need to save it in a personal location and it is currently in my drive, which they wouldn't want to keep the form in.

 

[GTO]

I am guessing that you want to be able to change the wb easily in place (where its stored), but not allow users to easily botch it up...

Not tested, but maybe try slowing them down a bit by marking it read-only if in the original folder and you're not the user.

Private Sub Workbook_Open()
    
    If Environ("username") = "YourUsername" Then
        If ThisWorkbook.ReadOnly Then
            ThisWorkbook.ChangeFileAccess xlReadWrite, "Your Password"
        End If
    Else
        If Not ThisWorkbook.ReadOnly And ThisWorkbook.FullName = "C:\Data\MyWBName.xls" Then
            ThisWorkbook.ChangeFileAccess xlReadOnly, "Your Password"
    End If
    
End Sub

Hope that helps,

Mark

 

[VoG]

The code I posted must go in the ThisWorkbook module. Press ALT + F11 to open the Visual Basic Editor. In the Project window double click ThisWorkbook then paste in the code.