change cell on exit vba

[Joost]

Hello,

Is it possible to clear cells when a workbook is closed with vba
automatically.
I can't figur it out who can help me

Thanks

 

[Guest]

You'd need to put your code to clear in a Workbook_BeforeClose event.

HTH,
Barb Reinhardt

 

[Guest]

Try this in the before_close workbook event:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set myRange = Worksheets("Sheet1").Range("A1:B100") '< Alter to suit
With myRange
.Select
.Clear
End With
ActiveWorkbook.Save
End Sub

 

[Vasant Nanavati]

A little shorter:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("A1:B100").ClearContents 'alter range to suit
End Sub

_____________________________________________________________________

 

[Guest]

and if your not familiar with events

http://www.cpearson.com/excel/events.htm

also, if unfamiliar with the command for clearing the contents of cells,
some examples.

With Worksheets("Sheet1")
.Cells.clearContents
End With

With Worksheets("Sheet2")
.Range("A1,B9,C57").ClearContents
End With

 

[Guest]

Just to add,
Unless it is a single sheet workbook, the results might be more satisfying
if the range is qualified with a specific sheet name. Otherwise, the command
assumes the activesheet and success clearing a specific range on a specific
sheet in Thisworkbook (assuming that is the objective) might be thwarted by
the user.

 

[Guest]

Success will also be thwarted if in the shorter version the user presses 'NO'
to save changes.

Macro - Something:- that does what you tell it to do and not what you want
it to do. Anonymous.