Results 1 to 9 of 9

Disable Save As with VBA

This is a discussion on Disable Save As with VBA within the Excel Questions forums, part of the Question Forums category; Hi All, I'm trying to stop users using "Save As" in their workbooks. Does anyone know how to do this? ...

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    479

    Default Disable Save As with VBA

    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
    Jay says, "When in Rome, eat lions!!"

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,497

    Default Re: Disable Save As with VBA

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    479

    Default Re: Disable Save As with VBA

    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
    Jay says, "When in Rome, eat lions!!"

  4. #4
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,497

    Default Re: Disable Save As with VBA

    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.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    479

    Default Re: Disable Save As with VBA

    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
    Jay says, "When in Rome, eat lions!!"

  6. #6
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,497

    Default Re: Disable Save As with VBA

    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.
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    479

    Default Re: Disable Save As with VBA

    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
    Jay says, "When in Rome, eat lions!!"

  8. #8
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,497

    Default Re: Disable Save As with VBA

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    Board Regular
    Join Date
    Oct 2007
    Location
    Chester
    Posts
    479

    Default Re: Disable Save As with VBA

    Works a treat! Thanks!
    Jay says, "When in Rome, eat lions!!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com