Results 1 to 5 of 5

Thread: Excel 2016: Readonly Workbook - Disable save/Save As

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    1

    Excel 2016: Readonly Workbook - Disable save/Save As

    I have a workbook with VBA programming behind it. It reads and saves data to Access.

    I open the workbook as ReadOnly in order to control updates to the workbook and programming. Users should not update - only controlled maintenance.

    On Close event saves the data to Access. On Open even reads data from Access.

    When Excel opens as ReadOnly, a user request to Save/Save As displays a denial message. I am not able to trap because Before Save is not executed at this time.

    Is there a way to reassign the save requests or disable this altogether? I have tried to do this via Command Bars.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Excel 2016: Readonly Workbook - Disable save/Save As

    Welcome to the (wrong) forum, Larry.

    Although someone will certainly be able to assist you in this part of vbforums.com, a better place to have put this thread is under the Office Development section, rather than the Visual Basic 6.0 and Earlier.

    I will notify a moderator to move it if he thinks it is appropriate, as you are using VBA (Visual Basic for Applications).

    Sam

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Excel 2016: Readonly Workbook - Disable save/Save As

    Thread moved to Office Development, as this seems to be entirely office related.
    My usual boring signature: Nothing

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Excel 2016: Readonly Workbook - Disable save/Save As

    A better solution might be to place the workbook in a folder that users only have read access to. That way any changes they might make cannot be saved back to that location. They can always save a copy to one of their folders and make changes there.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel 2016: Readonly Workbook - Disable save/Save As

    you should be able to use application.enablealerts = false to prevent the dialog, though note this will also disable other dialogs, then the beforeSave should work for you

    the before save should work anyway, even though the dialog shows first
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "workbook not saved"
    Cancel = True
    End Sub
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width