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.

 

[RoryA]

Welcome to the forum.

The workbook-level equivalent (which must be in the ThisWorkbook module) would be the Workbook_SheetChange event, which looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

To monitor different sheets, you should test Sh first, which is the sheet where the change is happening, then test if Target is among the cells of interest. For example:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh Is Sheets("Sheet3") Then
        If Not Intersect(Target, Sh.Range("A3:A5")) Is Nothing Then
            MsgBox "You changed a cell in A3:A5 on Sheet3"
        End If
    End If
End Sub

Does that help at all?

 

[Entregan]

A little. But, I am mostly still confused. Is it possible that you could break down the syntax and function, if it's not asking too much?

 

[Entregan]

Context might help. I'm creating an options page that contains a function to switch between Metric and Imperial measurements. If I change the binary option, I need the cell containing the existing measurements to clear or reset.

 

[RoryA]

That event responds to changing cells on a sheet, just like the Worksheet_Change event, except it is triggered for all sheets. It provides two arguments:
Sh is the sheet where the change is happening
Target is the range of cells that are being changed.

The equivalent of your earlier code:

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

would be something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' check if the change happened on Sheet15 and it was in I16
    If Sh is Worksheets("Sheet15") And Target.Address(0, 0) = "I16" Then
        ' need to disable events temporarily, otherwise changing another cell will trigger this event again
        Application.enableevents = False
        Worksheets("Sheet1").Range("B5").ClearContents
        Application.enableevents = True
    End If
End Sub

My preference is to use Intersect rather than testing the address of Target, since it handles a change to multiple cells.

 

[Entregan]

I should mention I'm also using dynamic drop downs with the INDIRECT function. Also; thank you for the welcome. I might be a little too tired to process all of this at the moment. However, I'm seeing it (sort of) but, I can't seem to get it to work.

 

[Entregan]

Also, I tested the code. It gave me a runtime 9 error out of range.

 

[RoryA]

If you have one options page, I can't see why you wouldn't just use the Worksheet_Change event for that page, since that is presumably what controls everything.

 

[Entregan]

I did try. Again though I know virtually nothing about VBA, and in my search for answers, I found a thread that said I needed to do so differently. The results of my understanding were in my original post, in the form of code. I apologize for the miscommunications.

 

[Entregan]

Also, that said, I found a formulaic work around. I would still like to know how to make the code work But, I do not (currently) require it. It just required a series of modifications to the lists and thereby formulas.
Either way, I appreciate the time and effort.