Clear Cells in VB

[wrightlefty]

Cell "E12" is populated by validation.

All other validated cells look to "E12" for information.

If the value in cell "E12" is changed, all other cells should have any value in them cleared and be ready for a new value to be selected.

The code I tried to use does not work...

Private Sub worksheets(Custom_Motor)
If Click.cells.["E12"] Then Range("e14,e16,e18,e20,e22").ClearContents
End Sub

Thanks for all your help.

 

[logius]

You need to put the code in the worksheet that you're using. From the VBA editor right click on the sheet you want to monitor and choose "View Code". Then, from the drop-down menu, select "Worksheet" and "SelectionChange". Swap out the test case with:

If Target = ActiveSheet.Range("E12") Then
   ActiveSheet.Range("e14,e16,e18,e20,e22").ClearContents
End If

That should be what you're looking for.

----------------------------------------
If you are reading this, then you have read too far...

 

[wrightlefty]

Thanks logius!

This did do what I asked; however, after seeing how the spreadsheet now operates I have a different question.

As mentioned before cell "E12" in populated via validation. Is it possible for the dependant cells to clear only if the value in "E12" changes, rather than just if someone clicks in it?

Thanks again.

 

[shetlandbob]

use Worksheet_Change instead

Robert Cumming

http://www.consultantengineer.co.uk

 

[wrightlefty]

Thanks Robert, it worked perfectly! Thanks again to both of you, very helpful!