change cell on exit vba
 

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

change cell on exit vba
 

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

HTH,
Barb Reinhardt
"Joost" wrote:

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

change cell on exit vba
 

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

"Joost" wrote:

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

change cell on exit vba
 

A little shorter:

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

__________________________________________________ ___________________


"Mike H" wrote in message
...
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

"Joost" wrote:

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

change cell on exit vba
 

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,C5:D7").ClearContents
End With

--
Regards,
Tom Ogilvy


"Joost" wrote:

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

change cell on exit vba
 

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.

--
Regards,
Tom Ogilvy


"Vasant Nanavati" wrote:

A little shorter:

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

__________________________________________________ ___________________


"Mike H" wrote in message
...
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

"Joost" wrote:

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

change cell on exit vba
 

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.


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Vasant Nanavati" wrote:

A little shorter:

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

__________________________________________________ ___________________


"Mike H" wrote in message
...
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

"Joost" wrote:

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