ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   clear cells when workbook is opened (https://www.excelbanter.com/excel-discussion-misc-queries/230964-clear-cells-when-workbook-opened.html)

Wanna Learn
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("C39:D39").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

Jacob Skaria
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("C39:D39").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("C39:D39").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

Jacob Skaria
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("C39:D39").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("C39:D39").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("C39:D39").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

Jim Thomlinson
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("C39:D39").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("C39:D39").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

Don Guillett
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("C39:D39").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
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("C39:D39").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

Don Guillett
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("C39:D39").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



All times are GMT +1. The time now is 01:22 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com