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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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