Worksheet change event

[merry_fay]

Hi,

I'm trying to run an event on the cell that has been changed in a worksheet change event.
This works in my excel where I don't move the cursor after enter, but when my colleagues use it, it then looks at the wrong cell because they move after enter.

Is there a way to say 'go to the last cell changed'?

I've got the code for Application.MoveAfterReturn = False but this will leave their setting changed (or if I set it back to true, change mine!!).

Thanks
merry_fay

 

[Andrew Poulsom]

It sounds like you are using Worksheet_SelectionChange rather than Worksheet_Change.

 

[MrKowz]

Can you please post your current code, as well as what exactly the code is meant to do? By the sounds of it, you are using Worksheet_SelectionChange, not Worksheet_Change.

 

[merry_fay]

OK, I think I worded it badly. I am definitely using worksheet_change.

The problem is in the end user's preferred settings.
After enter move cell down/up/left/right/don't move it.....

My macro involves using the cell that has just been changed so I need to refer to that cell (set it as activecell) regardless of the end user's settings.

Thanks

 

[RoryA]

You should be referring to Target not Activecell. Would be easier if you posted the code...

 

[MrKowz]

If you use Worksheet_Change(ByVal Target as Range), then Target will be the cell that was changed.

Can you please post your entire code?

 

[merry_fay]

OK, I think I worded it badly. I am definitely using worksheet_change.

The problem is in the end user's preferred settings.
After enter move cell down/up/left/right/don't move it.....

My macro involves using the cell that has just been changed so I need to refer to that cell (set it as activecell) regardless of the end user's settings.

Thanks

More precisely -on my laptop, I change a cell, hit enter & the activecell is still the cell I changed, on my colleagues laptops, they make a change, hit enter & the activecell is the one below the changed one.
Alternatively, someone could just type in a cell then click elsewhere without using enter.

I need to refer back to the cell just changed.

Thanks
merry_fay

 

[Jonmo1]

More precisely -on my laptop, I change a cell, hit enter & the activecell is still the cell I changed, on my colleagues laptops, they make a change, hit enter & the activecell is the one below the changed one.

Click Tools - Options - check "Move selection after Enter" - Select Down

I need to refer back to the cell just changed.

As stated above, Target is the cell(s) that was changed. Regardless of what cell is currently active.



If you want better help....post your code.

 

[merry_fay]

I think my point is still not being got.

It's not a case of changing the settings, I can't give a spreadsheet out to end users & ask them all to change their preferred settings, I need to make the code deal with their preferred settings.
As I said before, I could use Application.MoveAfterReturn = False but I would very fast become very unpopular!!!


In eg cell B9, the number 2 is typed.
The end user might then press enter & the active cell then moves or not depending on their settings, or the end user might just click in eg A1.

The macro needs to use the value in cell B9 so how do I get it to refer to B9 (in this instance, in another it might be D25)

Thanks

 

[RoryA]

I think it is you who are not getting the point.
The Change event provides you a range object called Target as an argument. This is the cell(s) that has (have) been changed. You need to refer to Target in your code and not to Activecell.
Once again, if you posted the code this would be much simpler...