Workbook_BeforeClose Question

[Guest]

I'm using this code to clear everything within the range of C14 to C21
(Sheet1) before my workbook closes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Range("C14:C21").ClearContents
End Sub

I wonder why it doesn't work. The data still remain (in the cells) if I
open it again. Is it because some cells have data validation on it? And
they're data input is restricted?

Or is there another way of making this work?

Help.

 

[Dave Thomas]

Your code has to be in ThisWorkbook to work. If it's in a module, it won't
get executed.

 

[Jim Cone]

You need to save the workbook after clearing the cells.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Sashi" <[email protected]>
wrote in message
I'm using this code to clear everything within the range of C14 to C21
(Sheet1) before my workbook closes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Range("C14:C21").ClearContents
End Sub

I wonder why it doesn't work. The data still remain (in the cells) if I
open it again. Is it because some cells have data validation on it? And
they're data input is restricted?
Or is there another way of making this work?
Help.

 

[Guest]

Hi Dave,

My code is already in ThisWorkbook, so it shouldn't be a problem.
Hello Jim,

I already added the line:

Worksheets("Sheet1").Save

after clearing the contents, it still didn't work.

Sashi

 

[Gord Dibben]

You cannot save a worksheet. You must save the workbook

Thisworkbook.save

or

ActiveWorkbook.save


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

I'm so sorry for being such a pain, but I tried your suggestion, leme show
you my whole code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Range("C14:C21").ClearContents
'ThisWorkbook.Save *I actually tried both*
ActiveWorkbook.Save
End Sub

These lines are in Sheet1. What else can I do?

 

[David Hilberg]

Private Sub Workbook_BeforeClose(Cancel As Boolean) ....
These lines are in Sheet1. What else can I do?

As Dave Thomas wrote, the code must be in the "ThisWorkbook" module. The
rule is determined by the name of of the subroutine. You have a
Workbook_ and not a Worksheet_ subroutine.

- David

 

[Gord Dibben]

Before_Close code must be entered into ThisWorkbook module.

Entering in a sheet module won't run it.


Gord