VBA code to save workbook with no prompt
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: VBA code to save workbook with no prompt
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Sep 2009
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Worked perfectly, thank you very much

    Quote Originally Posted by parry View Post
    Hi, save this macro in the ThisWorkbook object rather than in a module.

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
    End Sub

  2. #12
    New Member
    Join Date
    Jan 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Quote Originally Posted by svb View Post
    Try this...

    Sub SaveWbWithoutPrompt()

    Activeworkbook.Saved=True

    End Sub
    This code "Activeworkbook.Saved=True" does not save the file. Instead it only removes the Prompt message.

  3. #13
    New Member
    Join Date
    Dec 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: VBA code to save workbook with no prompt

    I'm using an excel document in a SharePoint lbrary as a template for a new document.
    I wanted to control everything so the user can’t change the FileName nor the path, so I don’t want any prompt at all. The methods Application.DisplayAlerts = False and ThisWorkbook.Saved still prompted the user when I use the method "ActiveWorkbook.SaveAs" in Sub Workbook_BeforeSave .


    The trick that did it for me is to use the cancel argument “cancel=true”, which I used after I saved the file.

    Problem solved.

    The code:
    Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Application.EnableEvents = False 'Don't want any other events getting fired off...
    Application.DisplayAlerts = False '...nor any "Are you sure?" warnings displayed.
    ActiveWorkbook.SaveAs Filename:=MyPathAndFileName & _
    ".xlsm" _
    , ReadOnlyRecommended:=False _
    , Password:="" _
    , WriteResPassword:="" _
    , CreateBackup:=False _
    , FileFormat:=52 ' or xlOpenXMLWorkbookMacroEnabled
    Cancel = True
    Application.EnableEvents = True 'RESETS EVENTS
    Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
    ActiveWorkbook.Saved = True
    End Sub

  4. #14
    Board Regular epardo87's Avatar
    Join Date
    Feb 2015
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Hi there,

    I'm new at this and I built a form, so I need that after filling the form, when hitting an OK button I placed, besides filling everything in the excel sheet and closing the form window (these is already working) I also need it to automatically save the workbook, so I paste the code ActiveWorkbook.Close SaveChanges:=True, and it works fine but I don't want it to close the workbook, how could I write it so that it only save changes without closing?

  5. #15
    New Member
    Join Date
    Dec 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Don't put your code in "Workbook_BeforeClose". Create another sub with another name, place your code there and have your button call that sub when clicked. Also put a call to that sub in Workbook_BeforeClose.

  6. #16
    New Member
    Join Date
    Jan 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Quote Originally Posted by epardo87 View Post
    Hi there,

    I'm new at this and I built a form, so I need that after filling the form, when hitting an OK button I placed, besides filling everything in the excel sheet and closing the form window (these is already working) I also need it to automatically save the workbook, so I paste the code ActiveWorkbook.Close SaveChanges:=True, and it works fine but I don't want it to close the workbook, how could I write it so that it only save changes without closing?
    Instead of using 'ActiveWorkbook.Close SaveChanges:=True' use only 'ActiveWorkbook.Save'

  7. #17
    Board Regular epardo87's Avatar
    Join Date
    Feb 2015
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Thanks, actually, after posting it, I kept searching and found ThisWorkbook.Save, and it worked, guess both do the same

  8. #18
    Board Regular rediffusion's Avatar
    Join Date
    Apr 2019
    Posts
    59
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Hello!
    Read all in this thread but it didn't help me!
    I need "Save all open workbooks or one by one after clicking on the cross".
    I find this code:
    Code:
    Dim wb As Workbook    For Each wb In Application.Workbooks    wb.SaveNext wb
    
    I Put it here:


    I have a "13.xlsx" open at the moment.
    I put VBA in "PERSONAL.XLSB" to code worked in all documents.

  9. #19
    Board Regular rediffusion's Avatar
    Join Date
    Apr 2019
    Posts
    59
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Arrow Re: VBA code to save workbook with no prompt

    MissClick. How do I edit my post?

    ок:
    Here is my code:

    Dim wb As Workbook


    For Each wb In Application.Workbooks
    wb.Save
    Next wb
    Application.DisplayAlerts = False


    I have nothing works help if you can!?

Some videos you may like

User Tag List

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
  •