Excel/VBA does to wrong cell using keyboard arrow keys

str8chat

New Member
Joined
Aug 17, 2023
Messages
2
Office Version
  1. 2019
  2. 2013
  3. 2007
Platform
  1. Windows
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:

1692282536034.png


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

1692282643458.png


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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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]

ABCDEFGHIJKLM
1
2OptionalRange
3M_1
4
5
6
7
8
9M_2M_3
10
11
12
13
Sheet1


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

VBA Code:
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
 
Upvote 0
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
ABCDEFGHIJKL
1
2
3
4A
5
6
7
8
9
10B
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:

VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top