Enter key moves active cell unexpectedly after running code

**GCW esq** · 11-25-2011, 10:22 PM

G'day.
Running Excel 2003 and XP Pro

INTRO:
This problem comes out of a larger chunk of code, in which I want users to be able to move only to cells where they need to make changes to cell values, and not to cells where they do not need to enter values. I'm trying to make it so that pressing the Enter key (when there is no change to make in a cell) will result in simply moving down one row (I would prefer to be able to make it go to a specific cell if there's no change, but I can't see any way of doing that... any ideas?). I have locked the non-use cells for change, but I need them to be selectable, so that the relevant part of the worksheet can be copied and pasted into a Word document.

THE ISSUE:
After running a macro (see below) in Excel, pressing the Enter key moves the active cell down one row and over to the column under the Target cell if two cells (or more) immediately under the target cell are merged. If they are not merged, the active cell simply moves down one row (in the same column) on Enter, as would be expected.

The code below checks for a change in cell A1, and if TRUE, moves the active cell to G8. Pressing the Enter key then moves the active cell to G9, but if cells A2 and B2 are merged, the active cell moves to A9 on pressing Enter. I don't want this!

Note: The same thing happens anywhere on a worksheet - not just at A1.

TEST:
To test this, cells A2 and B2 need to be merged. Then run the code by changing the value in A1 (active cell moves to G8). Then press Enter. On my PC, the active cell will now move to A9, instead of to G9.
Unmerge A2 and B2, redo the steps, and the active cell will then move to G9, as expected.

I've tried this on several PC's with the same result, but all use the same copy of Office 2003.

Please Login or Register  to view this content.

Thanks in advance for any help.

**pike** · 11-26-2011, 01:40 AM

Hey GCW esq

you need to get rid of merged cells in your design layout as they only cause problems

**GCW esq** · 11-26-2011, 04:02 AM

Thanks Pike. I see what you mean. Unfortunately, it's a bit hard to get rid of merged cells (there's a presentation element to this).
A couple of workarounds I tried were to put a dummy row under the target cell and hide the row (didn't work), or reduce the row height to zero (it worked down to a row height of 1 pixel, but not zero).
I thought if I could reassign the target cell to somewhere else, it might solve the problem, but I haven't managed to find a way to do that.

**pike** · 11-26-2011, 04:05 AM

can you attach the work sheet in a workbook?

**GCW esq** · 11-26-2011, 04:43 AM

Do you mean attach it to the Word document, or attach it to this thread?
I have managed a rather inelegant workaround - I have put a blank, unmerged row above and below the problem cell, and reduced their heights to 1 pixel, then shifted the border onto those cells, then reduced the height of the problem row by 2 pixels.
Is there no better way?