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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#6
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|