Sheet Change Event Not Running

[Hmerman]

Hello,
Hope you are well.

I have a simple macro that hides rows 120:256 if cell A2' value is ELK and hides rows 26:115 if A2 is LHP or KDP. The macro works. The values in A2 comes from a cell on another sheet with a pivot table that give the values ELK, LHP or KDP if I choose it with a slicer.

I tried activating the macro with a sheet change event, but it does not want to run.

If Target.Address = A2 then
Call hidRowsMaps
End If

I also tried:

If Not Intersect(Target.Cell(2, 1), Sheet1.Range("A2")) Is Nothing Then
Application.Run("hidRowsMaps()")
End If

But it did not work.

I also placed the macro code directly between the Target change code, but also no joy.

Can someone please give me advice on why the code will not work?

Regards,

 

[jproffer]

That won't make the change event fire. Enter the sheet code (right click on sheet tab). At the top of the page, in the left dropdown you can pick "worksheet"...in the right dropdown, pick "calculate" (or calculation or something....you'll see it). Put the same code as you had in the change event into the code that's created.

 

[Hmerman]

That won't make the change event fire. Enter the sheet code (right click on sheet tab). At the top of the page, in the left dropdown you can pick "worksheet"...in the right dropdown, pick "calculate" (or calculation or something....you'll see it). Put the same code as you had in the change event into the code that's created.

Thank you jproffer. I chose the calculate from the dropdown and it runs on changing with the slicer. The only thing is that now I get a Runtime error 424 "Object required" with the first line:

If Target.Address = Range("A2").Address Then
Call hidRowMap
End If

Why would I get this error?

Regards

 

[jproffer]

Calculation doesn't have a target.

It's hard to get a true test of things I suggest (speaking only for myself) without setting up the pivot tables (which are way over my head ), etc that you have....but maybe change Target.Address to ActiveCell.Address. Not even sure that will work, but it's simple enough to try it.

If not, someone much smarter than I can almost definitely help you figure it out.

 

[Hmerman]

Thanks. I did try

Activecell

but it did not work.

I then tried this:
Placing as a Workbook_Open event the following

Dim CalVal as Variant
   CalVal = Sheet1.Range("A2").Value

As a Worksheet_Calculate event the following

If Sheet1.Range("A2").Value <> CalVal then
  Call hidRowMap 'Hide rows macro
End If

It sadly also did not work, as this made excel crash.

So I did the next best thing and recorded a macro when I choose KDP, LPH and ELK with a pivot table slicer. I hid the slicer deep inside the spreadsheet and placed the the differing code for each choice plus the hidRowMap code in three Subs, then Assigned each macro to a rectangular object marked KDP or LPH or ELK. It works!!

It is not the ideal solution and I still do not know how to assign a value Caculate event to a single cell in Excel. But thank you very much for both your inputs. I learned a lot.