Delete VBA code in ThisDocument with a macro button

nEvErLoOz

New Member
Joined
Apr 18, 2016
Messages
14
I made a form in excel in which the cell O2 is a invoice number. This number increase by 1 everytime the document is open so each new invoice have a unique number.

To achieve this I put this VBA code directly in ThisDocument :

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("O2") = Worksheets("Sheet1").Range("O2") + 1
ActiveWorkbook.Save
End Sub

What this do is that everytime the master document (the empty form) is open it add +1 to the invoice number and immediately save the document so the next time this empty form is opened it will start from the new number and add + and so on. It works great.

At the end of the form there is a "Save" button which automatically save the completed forms in the same folder as the master empty document but using the new invoice number in O2 as a file name. Then automatically close the document so, the user will have to reopen a new empty form to make a new invoice. My code for this button is :

Sub SaveAsO2()
InvoiceNo = Range("O2").Value
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & "FL" & InvoiceNo & ".xlsm"
ActiveWorkbook.Close
End Sub

Everything works really great except for one thing. This newly saved invoice will have is invoice number in O2 increase by 1 too if we open it in the futur since it keep all the macro active.

So what I want is adding a line to my Save button which delete the code in ThisWorkbook before saving it.

Is it something possible? I'm open to any other way I can achieve what I want if you have a suggestion.

Thanks
Alex
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
replace this
VBA Code:
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & "FL" & InvoiceNo & ".xlsm"
With this
VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & "FL" & InvoiceNo & ".xlsx" FileFormat:=51
Application.DisplayAlerts = True
And see if that fixes you issue.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,932
Members
430,326
Latest member
tomwax46

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top