VBA disable save. Enable save as & save with vba macro-VBForums
Results 1 to 3 of 3

Thread: VBA disable save. Enable save as & save with vba macro

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    2

    Unhappy VBA disable save. Enable save as & save with vba macro

    Hi,
    I want to prevent users from using the "Save" function for my workbook.
    I want to allow 2 options for saving: "Save As" & VB script.

    Currently, they can save using a button linked to the following script:

    SubmitQuote()
    Range("C3").Value = Range("J12").Value
    Range("C11").Value = Range("J10").Value
    SaveName = Range("C3").Value
    SaveLocation = Range("J7").Value
    ActiveWorkbook.SaveAs Filename:=SaveLocation & SaveName & ".xls"
    End Sub

    I also have some script to disable saving, but it conflicts with the above script.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = False Then
    Cancel = True
    MsgBox "You cannot save this workbook. Use Save As"
    End If
    End Sub

    How can I modify the 2nd bit of code so that it won't interfere with the first?

    Thanks!

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: VBA disable save. Enable save as & save with vba macro

    Welcome to the forums

    This will steer you in the right path

    http://www.vbforums.com/showthread.php?t=505680
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    2

    Re: VBA disable save. Enable save as & save with vba macro

    Thank you Koolsid, for the warm welcome and quick response.

    However I think my situation is slightly different..

    Users must be able to save using a button that links to my save code (the first block of code). That button saves the file and automatically names it based on some cell values. This code never opens the SaveAs GUI. Which means that this method of saving is shut down by the 2nd block of code (which cancels the save if the SaveAs GUI is not TRUE.).

    Am I correct? If so, do you know if there are any other possibilities? By the way, I can't use the "read-only" approach. At my office I'm unable to set permissions. So I really need a VBA approach.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.