A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

clear cells when workbook is opened



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2009, 06:06 PM posted to microsoft.public.excel.misc
Wanna Learn
external usenet poster
 
Posts: 340
Default clear cells when workbook is opened

Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub

  #2  
Old May 15th, 2009, 06:15 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default clear cells when workbook is opened

Try the below

Private Sub Workbook_Open()
If ActiveSheet.Name = "Form" Then
Range("D7:F7").ClearContents
Range("C11").ClearContents
Range("D13").ClearContents
Range("G13:I13").ClearContents
Range("D15").ClearContents
Range("D17").ClearContents
Range("D19:J19").ClearContents
Range("D21:I21").ClearContents
Range("D25").ClearContents
Range("C29:J29").ClearContents
Range("C31:J31").ClearContents
Range("C33:J33").ClearContents
Range("C35:J35").ClearContents
Range("C37:J37").ClearContents
Range("C3939").ClearContents
Range("G39").ClearContents
Range("J39").ClearContents
Range("D41").ClearContents
Range("D56").ClearContents
Range("D7:F7").ClearContents
End If
End Sub


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


"Wanna Learn" wrote:

Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub

  #3  
Old May 15th, 2009, 06:19 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default clear cells when workbook is opened

If you want these cells to be blank; even if "Form" is not the active sheet
try the below

Private Sub Workbook_Open()
With Sheets("sheet1")
..Range("D7:F7").ClearContents
..Range("C11").ClearContents
..Range("D13").ClearContents
..Range("G13:I13").ClearContents
..Range("D15").ClearContents
..Range("D17").ClearContents
..Range("D19:J19").ClearContents
..Range("D21:I21").ClearContents
..Range("D25").ClearContents
..Range("C29:J29").ClearContents
..Range("C31:J31").ClearContents
..Range("C33:J33").ClearContents
..Range("C35:J35").ClearContents
..Range("C37:J37").ClearContents
..Range("C3939").ClearContents
..Range("G39").ClearContents
..Range("J39").ClearContents
..Range("D41").ClearContents
..Range("D56").ClearContents
..Range("D7:F7").ClearContents
End With
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below

Private Sub Workbook_Open()
If ActiveSheet.Name = "Form" Then
Range("D7:F7").ClearContents
Range("C11").ClearContents
Range("D13").ClearContents
Range("G13:I13").ClearContents
Range("D15").ClearContents
Range("D17").ClearContents
Range("D19:J19").ClearContents
Range("D21:I21").ClearContents
Range("D25").ClearContents
Range("C29:J29").ClearContents
Range("C31:J31").ClearContents
Range("C33:J33").ClearContents
Range("C35:J35").ClearContents
Range("C37:J37").ClearContents
Range("C3939").ClearContents
Range("G39").ClearContents
Range("J39").ClearContents
Range("D41").ClearContents
Range("D56").ClearContents
Range("D7:F7").ClearContents
End If
End Sub


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


"Wanna Learn" wrote:

Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub

  #4  
Old May 15th, 2009, 06:23 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default clear cells when workbook is opened

The code is event code so you can not just put it into a standard code
module. It must go into thisworkbook. Right click on the XL icon in the upper
left corner of the XL window and select view code to got their directly. The
add something like this...

Private Sub Workbook_Open()
with Worksheets("Form")
.Range("D7:F7").ClearContents
.Range("C11").ClearContents
.Range("D13").ClearContents
.Range("G13:I13").ClearContents
.Range("D15").ClearContents
.Range("D17").ClearContents
.Range("D19:J19").ClearContents
.Range("D21:I21").ClearContents
.Range("D25").ClearContents
.Range("C29:J29").ClearContents
.Range("C31:J31").ClearContents
.Range("C33:J33").ClearContents
.Range("C35:J35").ClearContents
.Range("C37:J37").ClearContents
.Range("C3939").ClearContents
.Range("G39").ClearContents
.Range("J39").ClearContents
.Range("D41").ClearContents
.Range("D56").ClearContents
End with
End Sub

--
HTH...

Jim Thomlinson


"Wanna Learn" wrote:

Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub

  #5  
Old May 15th, 2009, 06:35 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default clear cells when workbook is opened

Add more ranges within " " , as desired.

with sheets("Form")
Range("D7:F7,c11,d13,g13:i13").clearcontents
end with
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub


  #6  
Old May 15th, 2009, 06:51 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default clear cells when workbook is opened

But don't forget that leading dot in front of range:

.Range("D7:F7,c11,d13,g13:i13").clearcontents

or drop the with statement:

worksheets("Form").Range("D7:F7,c11,d13,g13:i13"). clearcontents



Don Guillett wrote:

Add more ranges within " " , as desired.

with sheets("Form")
Range("D7:F7,c11,d13,g13:i13").clearcontents
end with
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub


--

Dave Peterson
  #7  
Old May 15th, 2009, 06:54 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default clear cells when workbook is opened

Duh. Thanks for the catch.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave Peterson" wrote in message
...
But don't forget that leading dot in front of range:

.Range("D7:F7,c11,d13,g13:i13").clearcontents

or drop the with statement:

worksheets("Form").Range("D7:F7,c11,d13,g13:i13"). clearcontents



Don Guillett wrote:

Add more ranges within " " , as desired.

with sheets("Form")
Range("D7:F7,c11,d13,g13:i13").clearcontents
end with
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I want to clear some cells everytime the workbook is opened.
the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook
thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C3939").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub


--

Dave Peterson


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.