excel vba - contents erase

C

chief

is there a way to set up something or even a macro which once i print m
sheet, it will erase the numbers which i have in my cells, but not th
formulas i have set up in the formula bar. the reason being is m
invoice system has to obviously be cleared after each transaction, bu
i don't want to lose the formulas i have set up
 
J

JulieD

Hi Chief

there isn't (AFAIK) an "after print" event that you can tap into for the
code to run automatically, but there is a "before save" event which means
that if you put the following code into this event that every time you save
the document the fields will be cleared ready for the next invoice. if this
isn't suitable, you could include the code in an ordinary module & link it
to a button on the form.

to use this code you will need to select all the cells that you want to
clear the contents of - don't pick your formula cells just the cells you
type stuff into - and with them selected click in the name box and type a
name for the range (e.g. ToClear - dont' use spaces) and press enter.

Now right mouse click on the sheet tab and choose view code - on the left
hand side of the vb editor window you'll see your workbook's name in
brackets (if you can't see this choose view / project explorer) and
underneath it under microsoft excel objects you'll see "this workbook" -
copy & paste the following code into there replacing "toclear" with the name
that you used for the range:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("ToClear").ClearContents
End Sub

Hope this helps - let us know how you go.
Cheers
JulieD
 
Top