Carry out action when cells in Range have changed

0

I've created the following VB macro to call different functions when the cells in the Range (AS10:AS12) change in value e.g change from zero to greater than zero

However, the action in the subroutine BackOne is not carried out i.e it doesn't set L9 = "BACK"

Could someone please advise.

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

     ' The variable KeyCells contains the cells that will
     ' cause an alert when they are changed.
     Set KeyCells = Range("AS10:AS12")
     
     If Not Application.Intersect(KeyCells, Range(Target.Address)) _
            Is Nothing Then

     'Call Sub BackOne when cell AS10 changes
        If Range("AS10").Value > 0 Then
        Call BackOne
        End If
   End If
 End Sub
Sub BackOne()
' BackOne Macro
    Sheets("Form One").Select
    Range("L9").Select
    ActiveCell.FormulaR1C1 = "BACK"
End Sub
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Answers

0

Hi Cardano, this is a fairly common issue and has to do with how you select the place that you want to put the value.

You could solve this a few different ways, but the simplest is to replace the the BackOne() macro with this:

Sub BackOne()
' BackOne Macro

Sheets("Form One").Range("L9").Value = "BACK"

End Sub

You do not need to select a worksheet or a cell before you input a value into it.

If you wanted to show the worksheet still, you could put this line at the end of the BackOne() macro:

Sheets("Form One").Activate

To learn more about this I suggest checking out the TeachExcel YouTube channel and wait for the next two tutorials; they will go into more detail about how to avoid selecting worksheets and cells when workin with them.

(also, please put CODE tags around your code - i fixed it for you this time)

Let me know how this works!)

Update

To make it work when a formula changes you can use the Calculate event instead of the worksheet change event.

Private Sub Worksheet_Calculate()

'Call Sub BackOne when cell AS10 is greater than zero.
If Range("AS10").Value > 0 Then

    Call BackOne

End If

End Sub

However, note that this will run EVERY time something in that worksheet changes and not just when that specific cell was changed.

You can put as many IF statements as you want to manage all the potential conditions, or use a SWITCH statement, though IF is perfectly fine and easier to grasp when you are kind of new to Excel.

Also, if you don't want it to run each time a formula in the entire worksheet changes, let me know and we can work on a solution to that; however, it seems like it won't be an issue given what you're trying to do and that's why I didn't include a solution for that here.

Discuss

Discussion

Hi Don, (doesn't seem to work - I think I need to better explain my problem )
I have a range of date (AS10:AS25) , that if any of the cells in that range changes, I need to run  a different macro depending on which cell is changed. e.g BackOne() for cell AS10 changes, BackTwo() for AS11 changes, BackThree() for AS12 changes and so on ...upto BackSixteen() for AS25 changes. 
All the cells in the range are filled with IF statements and update automatically, with numeric values rather than data being entered manually
Could you please advise
Cardano (rep: 2) Mar 26, '19 at 4:37 pm
You need to pursue a slightly different strategy in this case. I will update the answer with another way to do it.
don (rep: 1616) Mar 27, '19 at 4:45 am
Add to Discussion
0

Hi Cardano.

I have tried this worksheet_change function and it does not recognise the change in the  results of an if function as being a worksheet change - Don can probably confirm that.

Steve

Discuss

Discussion

100% correct! :)
don (rep: 1616) Mar 27, '19 at 4:45 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login