Sleeper: Clear cell ranges if reference cell changes

[TGwilding]

Hello all,
I am trying to adjust a code so that it clears cell ranges T7:T33 and X7:X33 once cell value R50 (which is in another sheet) changes.
the code I am working off of is below:

Option Explicit 
Sub ClearEntries() 
On Error Goto noName 'error handler
'you can use the word "numbers" or any other range name in the next line
Application.Goto Reference:="numbers" 
Selection.ClearContents 
Range("A1").Select 
Exit Sub 
noName: 'only fires on an error
MsgBox "The named range you are calling does not exist." & vbCr & _ 
"You need to name a range as 'numbers' before proceeding.", _ 
vbOKOnly + vbInformation, "Named range required" 
End Sub

I do not need the msgbox in this but rather some sort of if cell R50 changes then clear cell ranges T7:T33, X7:X33.

Any help would be greatly appreciated.
thanks

[johnske]

Hi TGwilding, welcome to VBAX,

I've moved your post to the Excel Help forum, where it's more likely to be found. I've also wrapped your code in VBA tags to make it more readable for others (follow link in my sig to see how)

Regards,
John

[Bob Phillips]

Hello all,
I am trying to adjust a code so that it clears cell ranges T7:T33 and X7:X33 once cell value R50 (which is in another sheet) changes.

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Target.Address = "$R$50" Then
        Worksheets("Sheet2").Range("T7:T33,X7:X33").ClearContents
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.

[TGwilding]

Thanks for the reply.

I pasted this code into Sheet6 (Settings) where cell R50 is located. I referenced Sheet9 for the range that I wanted cleared. For some reason it is not working???? It is located as a macro in sheet6.
the code I used is below:

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$R$50" Then
Worksheets("Sheet9").Range("T7:T33,X7:X33").ClearContents
End If
ws_exit:
Application.EnableEvents = True
End Sub

[mdmackillop]

I think the problem lies in the meaning of "once cell value R50 (which is in another sheet) changes". The code you have posted will run if you select and change the value in cell R50, the target cell. It will not be triggered if this is changed because it contains a formula. The following tweak to your code will correct this, but in this case it relies on the sheet containing R50 to be activated and some change made causing the value in R50 to change. If this is not the case, please let us know the formula in cell R50.

Option Explicit
 Dim Monitored
Private Sub Worksheet_Activate()
Monitored = [R50].Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If [R50].Value <> Monitored Then
Worksheets("Sheet9").Range("T7:T33, X7:X33").ClearContents
End If
ws_exit:
Application.EnableEvents = True
End Sub

[TGwilding]

For some reason this code is not working. The value in cell R50 is a reference to a dropdown box in sheet9. When I choose a different item in the dropdown list the cell in R50 changes. The range in cell R50 is from 1 - 11. Any suggestions would be greatly appreciated.
thx again.

[mdmackillop]

To clarify, the value R50 on Sheet 6 depends upon a value on sheet 9. If the value on sheet 6 changes, certain cells on sheet 9 are cleared. If this is correct, the following code, pasted into sheet 9 should handle your requirement

Option Explicit
 
Dim Monitored
Private Sub Worksheet_Activate()
Monitored = Worksheets("Sheet6").[R50].Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Worksheets("Sheet6").[R50].Value <> Monitored Then
Worksheets("Sheet9").Range("T7:T33, X7:X33").ClearContents
Monitored = Worksheets("Sheet6").[R50].Value
End If
ws_exit:
Application.EnableEvents = True
End Sub

[TGwilding]

Thanks again for the help, but this code isn't working. I even tried adjusting it. Since R50 is not a formula, but rather referenced to a dropdown box, I set cell S50 = R50 and then used the code to reference S50 in settings. any ideas as to why it isn't working?

[mdmackillop]

The code relies on a WorkSheet change to trigger. Presumably the dropdown selection is not doing this. Can you possibly post an extract of your workbook showing how this referencing is done so I can try some other solution.