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

Perbjo

New Member
Joined
May 5, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
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).
 
Solution

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
May 5, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
Glad I could help somewhat. :)
 

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top