Clear Contents, Independent Instances; Separate Worksheet VBA

[Entregan]

Hello. New to the MrExcel.
I'm currently building a form that requires separate instances of cells being cleared based on cells from other worksheets changing.

Private Sub Worksheet_Change(ByVal Target As Range)    'When E1 changes, E2 clears, and c20, d20 .... j20 all clear.
    If Target.Address(0, 0) = "E1" Then Range("E2").ClearContents
    If Target.Address(0, 0) = "K7" Then Range("L7").ClearContents
    If Target.Address(0, 0) = "K8" Then Range("L8").ClearContents
    
    End Sub

As I understand moving to the workbook level and opening a module is necessary for multiple sheet references. However, I being a VBA newbie can't seem to figure it out.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = Worksheets("Sheet15").Range("I16") Then Range Worksheets("Sheet1")("B5").Clear.Contents
End Sub

Eventually I would lock and unlock certain cells, as well as have a reset button.
Any help would be greatly appreciated.

 

[Entregan]

I was wrong about that last bit. But, my appreciation stands.

 

[RoryA]

Assuming you are changing options directly on one sheet, its Worksheet_Change event should respond accordingly. From within that code, you can do pretty much anything you like to any sheet you need to. So, assuming your original code was meant to clear cells on Sheet 1 based on changes to cells in the option sheet, this code would go in the options sheet:

Private Sub Worksheet_Change(ByVal Target As Range)    'When E1 changes, E2 clears, and c20, d20 .... j20 all clear.
    If Target.Address(0, 0) = "E1" Then
       Worksheets("Sheet1").Range("E2").ClearContents
    ElseIf Target.Address(0, 0) = "K7" Then
       Worksheets("Sheet1").Range("L7").ClearContents
    ElseIf Target.Address(0, 0) = "K8" Then 
       Worksheets("Sheet1").Range("L8").ClearContents
    End If
    
End Sub

 

[Entregan]

I'm not sure why the Code provided wasn't working.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)    'When E1 changes, E2 clears, and c20, d20 .... j20 all clear.
    If Target.Address(0, 0) = "E1" Then
       Worksheets("Sheet1").Range("E2").ClearContents
    ElseIf Target.Address(0, 0) = "K7" Then
       Worksheets("Sheet1").Range("L7").ClearContents
    ElseIf Target.Address(0, 0) = "K8" Then 
       Worksheets("Sheet1").Range("L8").ClearContents
    End If

End Sub
However, I managed to solve my problem by removing the "Else If" statement based off of what your most recent code.

Private Sub Worksheet_Change(ByVal Target As Range)    
       If Target.Address(0, 0) = "I16" Then
       Worksheets("Character Sheet").Range("C5").ClearContents
    End If
        If Target.Address(0, 0) = "I17" Then
       Worksheets("Character Sheet").Range("C7").ClearContents
    End If
End Sub

 

[RoryA]

Well the sheet name and cell references are different.

 

[Entregan]

Indeed. I am trying to get both to work, as one is form data, with indirect references to dynamic ranges
Is an option on another sheet, that helps determine which data set to use for a particular set of fields.
The purpose is to remove inconsistencies when changing the parent cell by clearing the information from the child cell.