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").ClearContent s
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. :)

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

"Sashi" 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").ClearContent s
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. :)

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"
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").ClearContent s
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. :)

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

"Jim Cone" wrote:


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"
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").ClearContent s
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. :)

You cannot save a worksheet. You must save the workbook

Thisworkbook.save

or

ActiveWorkbook.save


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 08:04:01 -0700, Sashi
wrote:

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

"Jim Cone" wrote:


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"
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").ClearContent s
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. :)

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").ClearContent s
'ThisWorkbook.Save *I actually tried both*
ActiveWorkbook.Save
End Sub

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


"Gord Dibben" wrote:

You cannot save a worksheet. You must save the workbook

Thisworkbook.save

or

ActiveWorkbook.save


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 08:04:01 -0700, Sashi
wrote:

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

"Jim Cone" wrote:


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"
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").ClearContent s
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. :)

Sashi wrote:

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

Before_Close code must be entered into ThisWorkbook module.

Entering in a sheet module won't run it.


Gord

On Thu, 26 Jul 2007 11:44:11 -0700, Sashi
wrote:

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").ClearContent s
'ThisWorkbook.Save *I actually tried both*
ActiveWorkbook.Save
End Sub

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


"Gord Dibben" wrote:

You cannot save a worksheet. You must save the workbook

Thisworkbook.save

or

ActiveWorkbook.save


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 08:04:01 -0700, Sashi
wrote:

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

"Jim Cone" wrote:


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"
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").ClearContent s
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. :)