Macro on Enter or Tab

SByrne100

New Member
Joined
Dec 19, 2005
Messages
9
Hello,

I want to move to a specific cell if the user hits enter or tab. Can someone please advise how I assign a macro to run on hitting either of these two keys from a specific cell?

For example: When the user is in cell B6, I want the macro to run when the user hits Enter or Tab and move the cursor to cell E9.

Any ideas?

Many thanks.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Do you want to do this just when they change something in cell B6?
If so, do you want to still allow arrow keys or mouse selection to other cells?
 

SByrne100

New Member
Joined
Dec 19, 2005
Messages
9
Hi,

I want to force the user to a specific cell, so yes, I would want the macro to work even if they use the down or right arrow key.

Thanks!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$6" Then Exit Sub
If Target = "" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("E9").Select
End Sub

Copy the above code.
Right click the tab for the sheet you want this to happen on.
Click on View Code
Paste into white area.
Hit Alt-q.
When you save the workbook this code will be saved with it.
 

SByrne100

New Member
Joined
Dec 19, 2005
Messages
9

ADVERTISEMENT

Hi,

Thanks for the help. Can I clarify a little more please? the user may not enter anything in the first cell (B6), so it could be empty. The solution you have provided works if the user has entered data, but not if it is left empty

Any more ideas?

Thanks very much for your help!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, try this instead:

Code:
Public cAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If cAddress = "$B$6" Then Range("E9").Select
cAddress = Target.Address
End Sub
 

SByrne100

New Member
Joined
Dec 19, 2005
Messages
9

ADVERTISEMENT

That worked brilliantly!! Thanks very much!!
 

SByrne100

New Member
Joined
Dec 19, 2005
Messages
9
One more question - hopefully my last one on this thread!

I want to repeat this action in a different part of the spreadsheet, but this time the cells have been merged. I've tried the following but its not working:

Public cAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If cAddress = "$U$12:$BG:$12" Then Range("U14:BG14").Select
cAddress = Target.Address
End Sub


Any suggestions on how to adapt this to fit the merged cells?

Thank you.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Take out this line:

If Target.Cells.Count > 1 Then Exit Sub

but if you want to do this in different parts of the sheet, they will all have to reside in the same Worksheet SelectionChange event so perhaps something like this (with a small improvement):

Code:
Public cAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim d As String
Select Case cAddress
    Case "$B$6"
        d = "$E$9"
    Case "$U$12:$BG$12"
        d = "$U$14:$BG$14"
End Select
If d <> "" Then
    Application.EnableEvents = False
        Range(d).Select
    Application.EnableEvents = True
End If
cAddress = Target.Address
End Sub

If you have a lot of these, you might want to consider protecting the sheet instead.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,082
Messages
5,570,095
Members
412,311
Latest member
Mozz
Top