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
 

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

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,313
Messages
5,623,935
Members
416,001
Latest member
teabag

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