Select next row at column A after row has been deleted

[ipbr21054]

Morning,
I use the code below to allow the user to select a customer in column A which they wish to delete.
It works fine but has an issue once the row has been deleted.

This is the user is unable to use the navigation arrows to move from the selected cell.
The user has to select a cell then a mouse click before they can use the navigation arrow.

Example.
User selects a customer at A111
The code is run & that row is now deleted.
The user now sees the msgbox which confirms that customers row was deleted.
At this point the selected cell is A111 BUT using the navigation arrows does nothing.

So can you advise please once the user at A111 is deleted After the user cilcks OK to the confirmation message box the next cell is then selected Thus A112 in this case.
For future row deletions example A99 once deleted user clicks OK to confiremation message & the cell A100 is selected

Private Sub CommandButton3_Click()
   
    Dim answer As Integer

    If ActiveCell.Column = 1 Then
        answer = MsgBox("DELETE CUSTOMERS ROW " & ActiveCell.Value, vbYesNo + vbCritical)
        If answer = vbYes Then
            ActiveCell.EntireRow.Delete
            MsgBox "CUSTOMERS ROW NOW DELETED", vbInformation
        End If
    Else
        MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "SELECT CUSTOMER MESSAGE"
    End If

End Sub

 

[Snakehips]

@ipbr21054 Is your issue that you are test running this code from within the vba editor?

If so then you will need the click on the sheet to to get the focus back on the sheet rather than the editor.
If run from a button in the sheet it works fine for me.

 

[ipbr21054]

Hi,
Im using it on the sheet.
It deletes the customers row & then selects the same row reference example A111 it now now i am unable to move from that cell unless i use the mouse.

So currently if the case is it selects the same cell once the user clicks OK on the msgbox how would i add a line of code so something like,
Active cell + 1 so it the moves its focus from A111 to A112

 

[Snakehips]

Ok. After deletion of the selected row the data row below effectively moves up anyway but if you still want to select a row down then try this.

Private Sub CommandButton3_Click()
Dim answer As Integer
Dim r As Long
If ActiveCell.Column = 1 Then
r = ActiveCell.Row '**************
answer = MsgBox("DELETE CUSTOMERS ROW " & ActiveCell.Value, vbYesNo + vbCritical)
If answer = vbYes Then
ActiveCell.EntireRow.Delete
MsgBox "CUSTOMERS ROW NOW DELETED", vbInformation
End If
Else
MsgBox "YOU NEED TO SELECT A CUSTOMER IN COLUMN A", vbCritical, "SELECT CUSTOMER MESSAGE"
End If
Cells(r + 1, 1).Select '*************
End Sub

For me, after the running of the above, the arrow keys function normally without any need to hit enter key first.
Hope that helps.

 

[ipbr21054]

Perfect Woeks a treat.
I have a similiar issue so will make a new post.