Disable Save As with VBA

[TFCJamieFay]

Hi All,

I'm trying to stop users using "Save As" in their workbooks. Does anyone know how to do this?

So far I have...

Private Sub Workbook_Open()

    Dim oCtrl As Office.CommandBarControl

    MyName = StrConv(Environ("username"), vbUpperCase)

    If MyName <> "JAMIE.FAY" Then
        'Disable Save As
        For Each oCtrl In Application.CommandBars.FindControls(ID:=748)
                oCtrl.Enabled = False
        Next oCtrl
    End If

End Sub

...but it's not working.

Many thanks,

Jay

 

[VoG]

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
    MsgBox "Save As is disabled", vbInformation
    Cancel = True
End If
End Sub

 

[TFCJamieFay]

Hi VoG,

That works, however the user still needs to be able to save the workbook, just not create any copies. If there was a BeforeSaveAs that would be perfect!

Many thanks,

Jay

 

[VoG]

If the workbook is already saved then the code will only prevent Save As not Save.

If you are trying out the code on a workbook that has not yet been saved then the code will prevent Save as well.

Try commenting out the code, saving the workbook, uncommenting the code and testing again.

 

[TFCJamieFay]

Hi VoG,

I've tried that but it doesn't work. This is my full sub. I want to be able to create copies etc, but not anyone else. Is there something in here that is stopping it working correctly?

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

    Dim MyName As String
    
    MyName = UCase(Environ("username"))
    
    If MyName <> "JAMIE.FAY" Then
        'Disable Save As
        MsgBox "Save As is disabled", vbInformation
        Cancel = True
    End If

End Sub

Many thanks,

Jay

 

[VoG]

That works for me - when I tried it it wouldn't let me Save As. I then changed the username in the code to mine and it would.

 

[TFCJamieFay]

Hi VoG,

I don't think I was very clear with my last post sorry.

The users need to be able to use "save" in the workbook but not "save as". With this sub, unless your username is "JAMIE.FAY" you can't save at all. Do you have any ideas?

Many thanks,

Jay

 

[VoG]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim MyName As String
    If SaveAsUI Then
        MyName = UCase(Environ("username"))
        If MyName <> "JAMIE.FAY" Then
            'Disable Save As
            MsgBox "Save As is disabled", vbInformation
            Cancel = True
        End If
    End If
End Sub

 

[TFCJamieFay]

Works a treat! Thanks!

 

[Ali Ali]

HelloThe "Save to Name" code works well with the normal Excel file, but it is not active with the EXE filePlease help get an effective code with this qualityThank