Disable Save As with VBA

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
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...

Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try this

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try

Code:
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
 
Upvote 1
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top