Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > clear cells when workbook is opened
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 318
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("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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,520
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("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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,520
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("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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
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("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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
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("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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
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("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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
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("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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
open workbook b when workbook a opened [email protected] uk Excel Discussion (Misc queries) 5 July 23rd 08 01:02 AM
Unhide Row when workbook is opened Wanna Learn Excel Discussion (Misc queries) 8 October 19th 07 04:55 PM
activate opened workbook via VBA Noemi Excel Discussion (Misc queries) 1 May 3rd 06 01:21 PM
How to run a macro as soon a a workbook (.xls) is opened? Sangamesh Acharya Excel Worksheet Functions 1 December 15th 05 11:15 PM
Workbook copies itself when opened Sundevil249 Excel Worksheet Functions 4 November 30th 05 08:17 PM


All times are GMT +1. The time now is 12:26 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement