Excel/VBA does to wrong cell using keyboard arrow keys

[str8chat]

I have a spreadsheet with a mixture of mandatory and optional fields. Having filled one mandatory field, a right arrow should go to the next mandatory field, but optional fields that have been skipped can be selected using the mouse. In a few places a mandatory field consists of two or more merged cells to allow for extra data. When I enter a merged cell field with the arrow keys, and it subsequently jumps to the next mandatory cell, when I leave that next cell it can go to the wrong position. I have narrowed the problem down and it can be demonstrated in the following:

The VBA code attached to this cut down example is as follows:

Moving round the sheet using the keyboard arrow keys, if you go into 'A' it immediately jumps to 'B' and vice versa. However, if I go into 'A' and then try leaving 'B' with the arrow keys, the next cell selected is not one immediately next to 'B'. If I use the mouse to select the same sequence of cells, it works as I believe it should.

Is there a way of moving around such a worksheet with the arrow keys without this issue occurring?

I have tried it on Excel 2007, 2013 and 2019, all of which show the same effect. I have also set up a similar worksheet on Excel 97, which exhibits the same behavior. Advice will be appreciated. Thanks.

 

[StephenCrump]

Welcome to the Forum!

The code you've posted isn't responsible for the jumping from A to B. So I'm guessing you also have code for the Worksheet_Change event? Note that your Subs could be calling each other, e.g. your .Select will trigger another call to Sub Worksheet_SelectionChange.

If I was the user, I'd be annoyed if VBA code hijacked the cursor in this way, e.g. taking me to B because it was blank, when perhaps I'd prefer to first change the value I already had in A.

But perhaps something along these lines would work for you? [Note that I have used code tags </> to post the code, and XL2BB to post the screenshot, so that other forum users can easily copy/paste]

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2								OptionalRange
3					M_1
4
5
6
7
8
9									M_2		M_3
10
11
12
13
Sheet1

MonitoredRange: = Sheet1!$A$1:$L$12

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim r As Range
    Const N = 3
    Dim i As Long
    
    Set r = Range("M_1")
    For i = 2 To N
        Set r = Union(r, Range("M_" & i))
    Next i
    Set r = Union(r, Range("OptionalRange"))
    
    Range("MonitoredRange").Interior.ColorIndex = 40
    
    If Not Intersect(Target, Range("MonitoredRange")) Is Nothing _
        And Intersect(Target, r) Is Nothing Then
    
        Application.EnableEvents = False
        For i = 1 To N
            With Range("M_" & i)
                If .Value = "" Then
                    .Interior.ColorIndex = 3
                    .Select
                    Exit For
                End If
            End With
        Next i
        Application.EnableEvents = True
    End If
    
End Sub

 

[str8chat]

To Stephen Crump - thank you for replying to my original question.

I started self learning VBA about 20 years ago shortly before I retired, and although I have used Excel itself for many years, in is only really since COVID arrived that I have been broadening my knowledge of VBA. I have looked for various answers to many of the problems I have encountered on the Internet, but this one seems to be a long standing bug in Excel and its interaction with VBA.

Normally, without any VBA, if you step in to a merged group of cells using the arrow keys. When you use the arrow keys to leave that cell it will move to a cell in the same row or column as the cell in which the merged block was entered. However, if I have VBA code that jumps from a merged block of cells, somewhere else, when I next hit an arrow key, the next cell selected is affected by where the merged cell block was entered. The example I added was a very basic demo of the problem I am experiencing. If there are no merged cells it all works perfectly.

Sorry if I didn't use the right tools for uploading the original, I will hopefully do better this time:

Unstable Select.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4					A
5
6
7
8
9
10									B
11
12
Unstable select

On my original spreadsheet, the 2x2 merged group of cells 'A' and the other labelled cell 'B' both have broad outlines, as shown in my original posting. I guess that XL2BB doesn't capture this.
I have tidied the VBA code a little bit and added a few comment lines:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' If selected cell outside a 12 by 12 area don't react to the selection

    If Target.Row > 12 Or Target.Column > 12 Then

' If cell N1 is empty process the selection

    ElseIf Range("N1") = "" Then

' Set the 12 by 12 area to beige background

        Range("A1:L12").Interior.ColorIndex = 40
    
' Store the selected address in N1

        Range("N1") = Cells(Target.Row, Target.Column).Address

' If either box (A or B) is selected set N1 with the address of the other box

        If Range("N1").Value = "$E$4" Then
            Range("N1") = "I10"
        ElseIf Range("N1").Value = "$I$10" Then
            Range("N1") = "E4"
        End If

' Set the newly selected cell/box with a red background
' Then select that cell/box
' Since N1 (cell (1,14) is not empty at this point, a loop is prevented

        Range(Cells(1, 14)).Interior.ColorIndex = 3
        Range(Cells(1, 14)).Select
    End If

' Clear N1 to allow processing on the next move.

    Range("N1") = ""
End Sub

I am not using "Application.EnableEvents" as I prefer to set a marker in one specific cell while testing. Normally the A1 cell , which I tend to reserve for this function, and then check which cell has been selected in "Worksheet_SelectionChange". If it's A1 that is selected then I clear the marker and exit. If it is not A1 that is selected, but the marker is set, I exit without clearing the marker. I believe this has the same effect, but I can just click on A1 to normal processing again.

On the actual sample, if I start away from 'A' or 'B' selecting a random cell with the mouse, then use the arrow keys to go to 'B' it jumps back to 'A'. Subsequent steps using the keys are as expected. However, if I start as before and use the arrow keys to go to 'A', it jumps to 'B' but when I use the next arrow key to come out of 'B' it goes to the wrong location. If I select 'A' with the mouse it works perfectly - jumping to 'B' and coming out of 'B' exactly where the arrow key normally takes it.

This phenomon appears to have existed since at least Excel 97 which I have on an old Windows 98 PC.

The other thing I have done is to test a mandatory cell when I try to leave it. If the cell is empty my code reselects that cell in my actual project. I haven't included this in the example as it is just extra unnecessary complication around my query.

 

[StephenCrump]

The code you've posted isn't responsible for the jumping from A to B. So I'm guessing you also have code for the Worksheet_Change event?

Sorry this comment was incorrect. I didn't read your code properly.

However, if I have VBA code that jumps from a merged block of cells, somewhere else, when I next hit an arrow key, the next cell selected is affected by where the merged cell block was entered.

Yes, I can see the problem now, and can replicate it. At its simplest, if A is a merged range, and you use the up/down/left/right arrow to get to A, then after this code when the cursor ends up at B:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    If Target.Address = Range("A").Address Then Range("B").Select
    Application.EnableEvents = True

End Sub

... the up/down arrow keys will remember the column you entered A, rather than using the column for B, and
the left/right arrow keys will remember the row you entered A, rather than using the row for B.

Sorry, I can't see a simple way of making this work. You'd think that if the code unmerged/merged or made another single cell selection and called Worksheet_SelectionChange iteratively, that might make Excel/VBA let go of the merged cell references, but apparently not.

Another reason (to add to the many!) not to use merged cells.