Worksheet Change Event code help

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.

/**

Private sub worksheet_change(ByVal Target As Range)

if intersect (target, range("B4")) is nothing then
	if range("B4").value <> "(All)" then
		range ("X6").value = range ("B4").value
	end if
end if
End sub

**/

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

Untested, but try:

Private sub worksheet_change(ByVal Target As Range)

If Intersect (Target, Range("B4")) Is Nothing Then Exit Sub
If Me.Range("B4").Value = "(All)" Then Exit Sub
Sheets("Sheet2").Range("X6").Value = Me.Range("B4").Value
End Sub

Regards, TMS

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

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

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:

If Intersect (Target, Range("B4")) Is Nothing Then Exit Sub

... 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:

If Not Intersect (Target, Range("B4")) Is Nothing Then
     If Me.Range("B4").Value = "(All)" Then Exit Sub
     Sheets("Sheet2").Range("X6").Value = Me.Range("B4").Value
End IF

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

If Target.Address <> Range("B4").Address Then Exit Sub
If Me.Range("B4").Value = "(All)" Then Exit Sub
Sheets("Sheet2").Range("X6").Value = Me.Range("B4").Value

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:

If Not Intersect (Target, Range("B4")) Is Nothing Then
     Application.EnableEvents=False
     If Range("B4").Value = "(All)" Then Exit Sub
     Range("X6").Value = Range("B4").Value
     Application.EnableEvents=True
End IF

Regards, TMS

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

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

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