Sheet Change Event Not Running

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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.

Code:
If Target.Address = A2 then
Call hidRowsMaps
End If

I also tried:
Code:
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,
 
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.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:
Code:
If Target.Address = Range("A2").Address Then
Call hidRowMap
End If

Why would I get this error?

Regards
 
Upvote 0
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.
 
Upvote 0
Thanks. I did try
Code:
Activecell
but it did not work.

I then tried this:
Placing as a Workbook_Open event the following
Code:
Dim CalVal as Variant
   CalVal = Sheet1.Range("A2").Value

As a Worksheet_Calculate event the following
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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
Back
Top