Ques:- I would like to do the following. If cell A1 contains “Hello” and if the focus is on A1 and if the Enter key is pressed, then the focus should go to B2 and a MsgBox should appear. I think this can be done with key events, something like OnKeyDown, but how
Asked In :-
western power,
Right Answer:
You can achieve this using the `Worksheet_SelectionChange` and `Application.OnKey` methods in VBA. Here’s a sample code:
1. Open the VBA editor (ALT + F11).
2. In the relevant worksheet, add the following code:
```vba
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.OnKey "~", "GoToB2"
Else
Application.OnKey "~"
End If
End Sub
Sub GoToB2()
MsgBox "You pressed Enter!"
Range("B2").Select
End Sub
```
This code checks if the selected cell is A1 and assigns the Enter key to the `GoToB2` subroutine, which shows the message box and moves the focus to B2.
You can achieve this using the `Worksheet_SelectionChange` and `Application.OnKey` methods in VBA. Here’s a sample code:
1. Open the VBA editor (ALT + F11).
2. In the relevant worksheet, add the following code:
```vba
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.OnKey "~", "GoToB2"
Else
Application.OnKey "~"
End If
End Sub
Sub GoToB2()
MsgBox "You pressed Enter!"
Range("B2").Select
End Sub
```
This code checks if the selected cell is A1 and assigns the Enter key to the `GoToB2` subroutine, which shows the message box and moves the focus to B2.
