Change a VBA to run automatically or when moving tabs

[DMO123]

Hi All,

i have the below VBA which only runs when the cell is updated. there is a formula that updates the cell but the VBA is not picking this up as a change. how do i get the VBA to run automatically so i dont have to tab inside each cell to make the VBA run. preferably fully automatic but if its easy can be a change of tab etc.

any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Column = 9 Or Target.Column = 13 Then 'Column I or column M
       If UCase(Target) = "YES" Then Target.Offset(0, -1).ClearContents
    End If
End Sub

 

[Norie]

You could use the Calculate event instead of the Change event.

Alternatively if the formula you are trying to run this code for has references to cells where is manual input you could you could check for those cells being changed.

 

[DMO123]

@Norie - thanks. if im understanding this correctly i update

[COLOR=#333333]Private Sub Worksheet_Calculate(ByVal Target As Range)[/COLOR]

?

if this is correct i got the below error:
"Procedure declaration does not match description of event or procedure having the same name"

 

[Norie]

Unfortunately not.

The Calculate event is triggered whenever the sheet calculates and doesn't have a Target argument.

That's kind of one of the problems with using that event. it'll be triggered by the calculation of formulas that have nothing to do with the one you are interested.

 

[DMO123]

i see - would you be able to help me put this into my code to get it working?