I need to have a worksheet change event identify if a particular cell was changed and then reset the value in another cell if the changed cell ="yes". i.e. if d12 is changed, and its value is "yes", change d11 to zero. Can anyone help?

Bryan A. Brassell

Hello,


try this :

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("yoursheetname").Range("d12") )
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("yoursheetname")
If Target.Value <> "" Then
If Target.Value = "yes" Then .Range("d11").Value = 0
End If
End With
End Sub

Succes

Tried:

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "YES" Then .Range("d11").Value = 0
End If
End With
End Sub

and no luck. Did not get any error messages either. I do not understand what the first routine is doing?

Bryan A. Brassell

Bryan,

when you put/change something in a worksheet then
normally excell recalculates all the sheets in the workbook.
So this happens automatically.

That is a good trigger/event then to do your own routines.

(In the quick response a forgot a line of code)
Don't forget to put the code in your sheet "Input".

So again:

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "yes" Then .Range("d11").Value = 0
End If
End With
End Sub

Succes again.

This is getting closer - it now changes,however, it changes d11 to zero regardless of where the value YES is put (not just if it is put in cell d12. How do I get it to only change when cell d12 is "YES"?

Thanks for the help...

Bryan A. Brassell