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

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
>
>
>

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
>
>
>

A little shorter:

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

_____________________________________________________________________


"Mike H" <(E-Mail Removed)> wrote in message
news:0E5277AD-042F-4F4C-8392-(E-Mail Removed)...
> 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
>>
>>
>>

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

--
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
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:0E5277AD-042F-4F4C-8392-(E-Mail Removed)...
> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:0E5277AD-042F-4F4C-8392-(E-Mail Removed)...
> > > 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
> > >>
> > >>
> > >>
> >
> >
> >