Results 1 to 3 of 3

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

Hybrid View

  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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
  •  



Click Here to Expand Forum to Full Width