How to use If target.address on active sheet instead of a specific one?

[Perbjo]

Hi Experts

Once again I'm struggling with VBA.

My question is based on, that I have a formula that is copied to several sheets, and for each sheet it should be possible to run a macro when C10 on any active sheet is change to another value but only on the active sheet.
But is that even possible and if 'Yes, almost everything is possible within Excel' then how to do it?
Thanks in advance.

 

[Micron]

Sheet_Change event; can use Target property and execute if correct ( C10 ) and exit if not. However, IIRC this event will not fire if it's a formula that changes what is displayed in C10. I do not know if Calculate event can know the Target property property (cell that was edited but not by a formula).

 

[6StringJazzer]

I do not know if Calculate event can know the Target property property (cell that was edited but not by a formula).

It cannot. There is no Target argument for Calculate. One way this can be done is to use Change to look for changes in the precedent cells referenced in the formula, but this gets unwieldy if there are many of them or the precedents go back many steps.

If C10 is updated by entering a new value directly into the cell then [B]Micron[/B]'s solution is spot on.

 

[Perbjo]

Hi Micron
Thanks for your fast reply - and it seems to work

I use this code with SheetChange:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$C$10" Then
Call UgeDagNavne 'Danish for names of the weekdays
End If
End Sub

 

[Micron]

Glad I could help somewhat.

 

[Perbjo]

Glad I could help somewhat.

So glad you did