You are here:

Excel/Clear cells contents in one worksheet if cell changes in a different worksheet


Using Excel 2010

I have a worksheet named "Input_Employee" in which a status value (cell C16) may be changed by the user.  If this value changes, I wish to clear the contents of the cells in columns C through G in the worksheet "Employee DB" for the corresponding employee record.  Using the Worksheet_Change feature in the "Input_Employee" code, I am able to identify the correct row containing the employee data in the "Employee DB" worksheet, but I get a 1004 error on the following line:

Worksheets("Employee_DB").Range(Cells(Row_Counter, EE_BY_Form_Col), Cells(Row_Counter, EE_BY_Mgr_Col)).ClearContents

where Row_Counter is the employee record row in "Employee DB", EE_BY_Form_Col equals 3 (for column C) and EE_BY_Mgr_Col equals 7 9for column G).


since the Employee_DB sheet is not the activesheet then the unqualified Cells refers to the activesheet while the Range refers to the Employee_DB sheet.  This is an invalid range reference.  The solution is to qualify the cells references.   Here is one way

With Worksheets("Employee_DB")
  .Range(.Cells(Row_Counter, EE_BY_Form_Col), _
  .Cells(Row_Counter, EE_BY_Mgr_Col)).ClearContents

End With

Now everything refers to the Employee_DB sheet and the code should work.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.

[an error occurred while processing this directive]