Get value from another sheet

Question

I'm having some trouble trying to make this code work:

Private Sub Worksheet_Change(ByVal Target As Range) 
Static ZeroFlag As Boolean 
Dim KeyCells As Range 

Set KeyCells = Range("B29") 
On Error Resume Next 
Set KeyCells = Application.Union(KeyCells, KeyCells.Precedents) 
On Error GoTo 0 

If Not Application.Intersect(Target, KeyCells) Is Nothing Then 
    If (Range("B29").Value <= 0) Xor ZeroFlag Then 
        MsgBox IIf(ZeroFlag, "Not zero", "zero") 
        ZeroFlag = Not (ZeroFlag) 
    End If 
End If 
End Sub

The thing is, the alert only shows when I change the values from the sheet where "B29" is but i need the alert to show up when I alter values from other sheets too.

For example: The "B29" cell is located on sheet A and is the result of A1-A2, A2 is getting its value from cell A1 at the B sheet but the alert is only showing if I alter cell A2 from the A sheet and not A1 from B sheet.

What can I do to make it work?

When I first started do work on this code i looked like this and it work when I altered the value from diferents sheets

Private Sub Worksheet_Calculate() 
If Range("B29").Value <= "0" Then 
    MsgBox "Zero" 
Else 
    MsgBox "Not Zero" 
End If End Sub

But this code does not have the rules that i need.

Until the cell is bigger than 0 the alert does not show up, when the value is below 0 the alert shows "Zero" and as long as the value remains below 0 it stop showing, the moment the value is bigger than 0 the alert shows " Not Zero" as long as the value remains above 0 it stop showing.

What can I do to make it work?

Thanks

Nice @KSSheon, It seems you can only get reference to the sheet which was changed and not the specific cell, obviously there could be numerous cells which changed. — Jean-Pierre Oosthuizen, Mar 17 '16 at 11:55
Possible duplicate of [How can I run a VBA code each time a cell get is value changed by a formula?](http://stackoverflow.com/questions/4388279/how-can-i-run-a-vba-code-each-time-a-cell-get-is-value-changed-by-a-formula) — Jean-Pierre Oosthuizen, Mar 17 '16 at 12:25
It didn't work either, the alert show's up if i change the value manually on the sheet but if i use another sheet to get the value the alert doesn't show up — Luis, Mar 17 '16 at 12:37
You are trying to [Union](https://msdn.microsoft.com/en-us/library/office/ff834621.aspx) cells from different worksheets. You cannot do that. You are not receiving an error because of the `On Error Resume Next`. — , Mar 17 '16 at 12:58

score 1 · Answer 1 · answered Mar 17 '16 at 11:30

1

Instead of writing code in Worksheet_Change you'll have to write the code in Workbook_SheetChange as:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '....write your code here

End Sub

answered Mar 17 '16 at 11:30

Mrig

11,612
2
13
27

Didn't work, when i changed from Change to SheetChange the code stop working. It compiled but stopped showing the alert even when the change was done on the same sheet – Luis Mar 17 '16 at 11:48
I just want to confirm whether you just changed "Change to SheetChange" or "Worksheet_Change to Workbook_SheetChange" as mentioned by me? – Mrig Mar 17 '16 at 12:44
I've changed from WorkSheet_Change to Workbook_SheetChange – Luis Mar 17 '16 at 12:56

score 0 · Answer 2 · answered Mar 17 '16 at 14:40

It's working I've altered the first code that i've used and included some of the other code and it ended up like this

Private Sub Worksheet_Calculate()    Static ZeroFlag As Boolean 
If Range("B29").Value <= "0" Xor ZeroFlag Then 
    MsgBox IIf(ZeroFlag, "Not Zero", "Zero") 
    ZeroFlag = Not (ZeroFlag) 
End If
End Sub

Thanks everyone for the help

Get value from another sheet

2 Answers2