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]

Not sure if it matters, but try:

 If Target.Address = Range("A2") Then

(or you may have to add the .Address behind the range A2 as well...not sure)

You shouldn't need to specify the sheet name/index because it's a sheet change event.

 

[Joe4]

If Target.Address = Range("A2") Then
(or you may have to add the .Address behind the range A2 as well...not sure)

Yes, you would need the .Address at the end of Range("A2") if you do it that way.
Note that .Address returns a string, and you cannot compare a string directly to a range variable.

 

[jproffer]

Thanks Joe

 

[Hmerman]

Thanks. The spreadsheet is at work, will give feedback tomorrow (RSA time).

 

[Joe4]

You are welcome.

Just for fun, here are some other ways to go about it:

If Target.Address = "$A$2" then

If Target.Address(0,0) = "A2" then

 

[Hmerman]

Morning,
I added:

Range("A2").Address

But still the change event did not run.

Then after changing the value in A2 from ELK to LHP with the slicer I clicked inside A2 and pressed enter. The change event fired and the macro ran!
So what is odd is that the change event does not read the value changes with the pivot table slicers.

So my solution is to remove the slicer and place three button named ELK, LPH and KDP with the following macro (filters visible = True accordingly):

With Sheet1
    .PivotTables("PivotTable1").PivotFields("GEDEELTE")[COLOR=#ff0000].CurrentPage[/COLOR] = "(All)" [COLOR=#008000]'What can I replace CurrentPage with so it runs from Sheet1?[/COLOR]
    With .PivotTables("PivotTable1").PivotFields("GEDEELTE")
        .PivotItems("ELK").Visible = True
        .PivotItems("LPH").Visible = False
        .PivotItems("KDP").Visible = False
    End With
    Range("A2").Formula = "=GRAFIEK!$B$3" [COLOR=#008000]'Activates sheet change event[/COLOR]
    
End With

The only thing is I do not know what to replace .CurrentPage (in red) with to make the macro run without error 1004 "Method Pivotables or Object _Worksheet failed".

Can someone give me advice or a different solution?

 

[Hmerman]

Update:

As the pivot table is on Sheet3 and I want to run the filter from Sheet1. I changed With Sheet1 to

With Sheet3
...code as above
End with

Still get an error 1004 "Unable to get Pivotfields property from the PivotTables class".

Please assist?

 

[Joe4]

A few things about Worksheet_Change event procedures...

- They are placed on the sheet you are tracking the changes from.

- They are only triggered when a MANUAL update happens (i.e. someone types a value into a cell, or copy and pastes a value into a cell).
They are not triggered by changes due to formulas or calculations. You would need a Worksheet_Calculate event procedure for that.
If the value in A2 is changing due to a manual change elsewhere, write your Worksheet_Change event to capture that other manual change.

 

[Hmerman]

Thank you Joe. I did not know that.

Will try something along the line of
<code>

Application.Calculation = xlCalculationManual

...code

Application.Calculation = xlCalculationAutomatic
[FONT=arial]

Will give feedback.[/FONT]
</code>