PDA

View Full Version : Sleeper: Clear cell ranges if reference cell changes



TGwilding
06-19-2005, 03:14 PM
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
06-19-2005, 03:33 PM
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
06-20-2005, 01:52 AM
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
06-20-2005, 02:46 PM
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
06-20-2005, 04:16 PM
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
06-20-2005, 07:08 PM
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
06-20-2005, 11:55 PM
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
06-21-2005, 02:05 PM
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
06-21-2005, 02:10 PM
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.