VBA code to save workbook with no prompt

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Midknight63

New Member
Joined
Dec 2, 2014
Messages
2
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
 

epardo87

Board Regular
Joined
Feb 24, 2015
Messages
54
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?
 

Midknight63

New Member
Joined
Dec 2, 2014
Messages
2
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.
 

salimohammed007

New Member
Joined
Jan 3, 2014
Messages
2
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':)
 

epardo87

Board Regular
Joined
Feb 24, 2015
Messages
54
Thanks, actually, after posting it, I kept searching and found ThisWorkbook.Save, and it worked, guess both do the same
 

rediffusion

Board Regular
Joined
Apr 24, 2019
Messages
60
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:
[COLOR=#000000][FONT=Consolas]Dim wb As Workbook    [/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=For"][COLOR=#1990B8]For[/COLOR][/URL][COLOR=#000000][FONT=Consolas] Each wb [/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=In"][COLOR=#1990B8]In[/COLOR][/URL][COLOR=#000000][FONT=Consolas] Application[/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=."][COLOR=#A67F59].[/COLOR][/URL][COLOR=#000000][FONT=Consolas]Workbooks    wb[/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=."][COLOR=#A67F59].[/COLOR][/URL][COLOR=#000000][FONT=Consolas]Save[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Next wb
[/FONT][/COLOR]
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.
 

rediffusion

Board Regular
Joined
Apr 24, 2019
Messages
60
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!?
 

rediffusion

Board Regular
Joined
Apr 24, 2019
Messages
60
Re: 0Key I find solution!

Put code in ThisWorkbook:

Code:
Private Sub Workbook_Open()
    Application.VBE.MainWindow.visible = True
    Me.VBProject.VBComponents("TEST").Activate
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,101,906
Messages
5,483,654
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top