Excel VBA: Save Workbook without a Prompt (with Easy Steps)

When saving a Macro file in the exact location as before, it always prompts you to replace the file with an alert message. It can be inconvenient when you have to save data frequently and have limited time to do so. As a result, we’ll use a VBA code to resolve the problem. This tutorial will show you how to save a workbook without a prompt with Excel VBA.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Easy Steps to Save a Workbook without Prompt with Excel VBA

We’ve included a sample data set in the image below to demonstrate the difference after running the VBA codes to stop the prompting. While saving the file, we’ll use VBA codes to disable the display alert message.

Sample Data

Step 1: Create a Module to Write VBA Code

  • Firstly, press  Alt  F11  to open the VBA Macro.
  • Click on the Insert tab.
  • From the options, select the Module to create a new Module.

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 2: Insert SaveAs Function in Code

  • To use the SaveAs function in this workbook, type () and SaveAs, or select SaveAs from the list of available functions.

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 3: Add File Address and Name in VBA Code

  • To insert the file address, go to the file location.
  • Click on the folder icon and copy the address.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • Paste the address under the Filename argument of the SaveAs function.
  • After entering the address, type the file name.
  • Finally, close the address and the file name with inverted commas (” “).

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • After adding the address, type a comma and write 52 for the FileFormat argument.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • That is how the final codes will appear.
Sub Without_Prompt()
ThisWorkbook.SaveAs "C:\Users\Admin\Desktop\Bhubon\Exceldemy\3875_420093_Bhubon_excel vba save workbook without prompt\Save Without Prompt", 52
End Sub

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 4: Run VBA Code and Check for Error

  • A prompt message will pop up as we didn’t disable the Alerts message.
  • Then, click No.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • Finally, click on End.

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 5: Write a Code to Disable Prompt Message

  • To disable the prompt message, we have to disable the DisplayAlerts message.
  • Give me the DisplayAlerts. option statement as False to disable the Prompting or alarming message.
  • Write down the following codes in the Module to stop the prompt.
Sub Without_Prompt()
'Command to not to show the Prompt
Application.DisplayAlerts = False
'Apply command to save the file
'In the File Name argument give the file name and address
ThisWorkbook.SaveAs "C:\Users\Admin\Desktop\Bhubon\Exceldemy\3875_420093_Bhubon_excel vba save workbook without prompt\Save Without Prompt", 52
Application.DisplayAlerts = True
End Sub

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 6: Run VBA Code After Disabling DisplayAlerts Command

  • This time, you click on the run button or press  F5  to run the VBA codes.
  • But no alerting message will be prompted now.

Sample Data


Step 7: Check Final Response in Result

  • In our sample data set, change the recipient’s name from Brine to Bhubon.

Sample Data

  • Press   F5  to run the program and no message will pop up.
  • Close the worksheet without saving.

Sample Data

  • Open the file, and see that the file has been saved with the latest current edition you have made.

Sample Data


Conclusion

I hope this article has given you a tutorial about how to save workbooks without a prompt with  Excel VBA. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo