Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!

Hi Cheryl

From workbook launch VBE using short-key Alt+F11. From the left treevew
double click 'This Workbook '. Paste the below code and try selecting each
sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name < "Instructions" Then _
Range("C3:C12").ClearContents
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!

Hi Jacob,

Thanks for you fast response. In the left treeview, I have 2
"ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject
(PERSONAL2.XLS). Both seem to have the same stuff in them.

I double clicked the first "This Workbook" and copied the code you provided,
did a save, then cycled through my worksheets. Only one of them (Sheet9,
"SEP3 Support) actually has data in C3:C12, but nothing happened when I chose
that sheet.

Cheryl

"Jacob Skaria" wrote:

Hi Cheryl

From workbook launch VBE using short-key Alt+F11. From the left treevew
double click 'This Workbook '. Paste the below code and try selecting each
sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name < "Instructions" Then _
Range("C3:C12").ClearContents
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!

--Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11.

-- Select the correct workbook from the treeview..

VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook'
Select 'This Workbook' Right click View Code

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Hi Jacob,

Thanks for you fast response. In the left treeview, I have 2
"ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject
(PERSONAL2.XLS). Both seem to have the same stuff in them.

I double clicked the first "This Workbook" and copied the code you provided,
did a save, then cycled through my worksheets. Only one of them (Sheet9,
"SEP3 Support) actually has data in C3:C12, but nothing happened when I chose
that sheet.

Cheryl

"Jacob Skaria" wrote:

Hi Cheryl

From workbook launch VBE using short-key Alt+F11. From the left treevew
double click 'This Workbook '. Paste the below code and try selecting each
sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name < "Instructions" Then _
Range("C3:C12").ClearContents
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!

Thanks, Jacob! That worked. I didn't even have to reset the security. I
finally found (I can be so blind sometimes!) the proper "This Workbook" under
the tree, copied the code there, and everything worked!

Thanks again for your really fast and on target help!

Cheryl

"Jacob Skaria" wrote:

--Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11.

-- Select the correct workbook from the treeview..

VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook'
Select 'This Workbook' Right click View Code

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Hi Jacob,

Thanks for you fast response. In the left treeview, I have 2
"ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject
(PERSONAL2.XLS). Both seem to have the same stuff in them.

I double clicked the first "This Workbook" and copied the code you provided,
did a save, then cycled through my worksheets. Only one of them (Sheet9,
"SEP3 Support) actually has data in C3:C12, but nothing happened when I chose
that sheet.

Cheryl

"Jacob Skaria" wrote:

Hi Cheryl

From workbook launch VBE using short-key Alt+F11. From the left treevew
double click 'This Workbook '. Paste the below code and try selecting each
sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name < "Instructions" Then _
Range("C3:C12").ClearContents
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!

For future reference (assuming versions of Excel *prior* to 2007), you can get to the "ThisWorkbook" code window of the workbook you are viewing by right clicking the small icon immediately to the left of the menu bar's File item and selecting View Code from the pop up menu that appears.

--
Rick (MVP - Excel)


"Cheryl" wrote in message ...
Thanks, Jacob! That worked. I didn't even have to reset the security. I
finally found (I can be so blind sometimes!) the proper "This Workbook" under
the tree, copied the code there, and everything worked!

Thanks again for your really fast and on target help!

Cheryl

"Jacob Skaria" wrote:

--Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11.

-- Select the correct workbook from the treeview..

VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook'
Select 'This Workbook' Right click View Code

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Hi Jacob,

Thanks for you fast response. In the left treeview, I have 2
"ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject
(PERSONAL2.XLS). Both seem to have the same stuff in them.

I double clicked the first "This Workbook" and copied the code you provided,
did a save, then cycled through my worksheets. Only one of them (Sheet9,
"SEP3 Support) actually has data in C3:C12, but nothing happened when I chose
that sheet.

Cheryl

"Jacob Skaria" wrote:

Hi Cheryl

From workbook launch VBE using short-key Alt+F11. From the left treevew
double click 'This Workbook '. Paste the below code and try selecting each
sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name < "Instructions" Then _
Range("C3:C12").ClearContents
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Cheryl" wrote:

Excel 2003 (11.8307.8221) SP3
Windows XP

I have a workbook that contains 15 worksheets. The first one is an
"Instructions" page which contains links to the other 14. When any of the
other 14 are selected, I would like to automatically clear C3 through C12. Is
there an easy way to do this?

Thanks!