Worksheet Change Event code help

[kev_33]

Hi All,

I would like the following to occur on my report:
A change in value on cell B4 on Sheet1 runs a macro that changes cell X6 in Sheet2 (which is a hidden sheet) to the same value.

I tried a sample code (below) putting B4 value on Sheet 2 and keeping it visible; and this worked but after changing the cell value of X6, it goes into "Calculate" mode, and the excel gets stuck such that I have to end the Excel process from windows task manager.

/**

Please Login or Register  to view this content.

**/

Any help/ ideas on how to get this done?? Thanx in advance.

[TMS]

Untested, but try:

Please Login or Register  to view this content.

Regards, TMS

[kev_33]

Hi TMS,

Thanks for your help but it didn't work. It exits the sub after this line of code "If Intersect (Target, Range("B4")) Is Nothing Then", when I manually make a change to cell B4.
Plus after I remove the "exit sub", it modifies the cell X6, but I get the issue raised above where the excel starts calculating (I don't know what since there's really not much for it to take soo much time to calculate). When I hit the Esc button, it throws the error message "Code Execution has been interrupted". But clicking on any of the buttons (Continue, End or Debug) on the message makes it start calculating again until I end the excel process.

[kev_33]

Hi,

I'd like to understand the difference between:
If Intersect (Target, Range("B4")) Is Nothing, and
If Not Intersect (Target, Range("B4")) Is Nothing

What exactly is this intersect function that seems to be always used for the worksheet_change event code?

[JosephP]

intersect tells you whether two ranges have any cells in common-it returns either the common range or nothing. if you changed b4 then intersect(target, Range("b4")) would not be nothing

[kev_33]

But if that's the definition, it skips after the line If Not Intersect (Target, Range("B4")) Is Nothing, to the "End If" when I make a manual update to cell B4, and doesn't go into my VBA Code. It keeps going in a loop between the If and End If statements.
And do I need to use an intersect when I only want to check if a single cell's value has been updated or is there a better way??
Also, if I'm using a worksheet_change event, does it calculate the entire workbook after running my vba code? (coz that's what seems to be happening to me here, and I don't understand why or how to stop it).

Thanks for the info

[JosephP]

it skips after the line If Not Intersect (Target, Range("B4")) Is Nothing, to the "End If" when I make a manual update to cell B4

that is not possible. if you believe it to be the case please provide a workbook

And do I need to use an intersect when I only want to check if a single cell's value has been updated or is there a better way??

that is the best way-why don't you want to use it?

if I'm using a worksheet_change event, does it calculate the entire workbook after running my vba code?

only if you do something in the code to cause it or the change you made triggers a recalculation anyway. a change event does not of itself inherently trigger a recalculation

[TMS]

The code as provided does work as expected. Which maybe implies you didn't just copy and paste it.

JosephP has explained the Intersect function. But there are different ways of testing if the cell that has been changed is the (or a) cell that you are interested in.

I prefer:

Please Login or Register  to view this content.

... because that says if the Target cell isn't B4, exit the Change event code. Then you don't have all the rest of your event code nested in an IF ... End IF (and maybe forget to put the last End If in). It's perhaps more use for a longer range or a whole column but I always use this method

So, the alternative is:

Please Login or Register  to view this content.

If you just want to check one cell, you can say, for example:

Please Login or Register  to view this content.

That said, I think you end up typing more to get less.

Anyway, that's your choice. Incidentally, although I put it in here, you don't actually need the ".Me"


Note that I have qualified the output address with Sheets("Sheet2"). If you don't do that, you will be making a change to another cell on the same sheet. Which might explain the looping.

If you are making changes on the same sheet, you need:

Please Login or Register  to view this content.

Regards, TMS

[JosephP]

I always use intersect rather than checking addresses because it is possible to change more than one cell at a time and one (or more) of them may be the cell(s) I am interested in

[TMS]

Good point. And then there are choices about what you expect and what you want to do. For example, you may only be interested in one cell but someone may select a whole block (including the one cell) and press delete to clear the contents. Or array enter data ... Endless possibilities

because that says if the Target cell isn't B4

So, to clarify the above: because "Target" is a range, it can be more than one cell. So, what you are actually saying is, "is cell B4 one of the cells in the Target range?"


Regards, TMS

[kev_33]

Guys, thank you so much for all your help!! It works now. I was missing the following line which suddenly got it to work right:
Application.EnableEvents=False

Regards,
Kev

[kev_33]

Sorry guys, looks like it was too early for me to celebrate yet..
I need to modify this code such that it doesn't exit sub after this line -If Range("B4").Value = "(All)"
but instead remove the country filter in Report Filters from the pivot table named "pg1_02". Help??

[TMS]

Record a macro to remove the filter ... I have no idea what you are doing so I can't provide it. Then change the Exit Sub to an If ... Then ... Else ... End If.

It's really a new question because the original has been answered.

Regards, TMS