macros - navigating
 

How do I make a macro force my spreadsheet to jump to the next cell after I
hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go
to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc?
Thanks
Jeff

macros - navigating
 

Jeff

If you unlock those cells and protect the sheet, then you can tab from
unprotected cell to unprotected cell. Enter still doesn't work like that,
though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab from
unprotected cell to unprotected cell. Enter still doesn't work like that,
though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the tab
order doesn't mesh with left to right top to bottom on my form. The link that
has the change function looks like it would be perfect but I couldn't get it
to work. Here's the code from the link below. I copied this into a module in
my spreadsheet. It doesn't seem to execute at all. Not sure if the module is
wrong, or more likely how I put it into my excel spreadsheet in the modules
that is wrong. Any additional assistance would be appreciated.
Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

€˜Set the tab order of input cells
aTabOrder = Array(€�A1€³, €œC1€³, €œG3€³, €œB5€³, €œE1€³, €œF4€³)

€˜Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
€˜If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
€˜If the changed cell is the last array element
If i = UBound(aTabOrder) Then
€˜Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
€˜Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab from
unprotected cell to unprotected cell. Enter still doesn't work like that,
though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

"Jeff" wrote:
Protecting the sheet and unprotecting cells doesn't do it since the tab
order doesn't mesh with left to right top to bottom on my form.

Have you tried using OnKey? The following call:

Application.OnKey "~", "YourNavigationProcedure"

will hijack the enter key. After you call this method,
YourNavigationProcedure will execute when someone hits Enter, rather than
doing the normal Enter thing (however you have that configured.)

Application.OnKey "~"

Resets the default behavior of the Enter key.

Then all you'd have to do is write YourNavigationProcedure and figure out
how you know when it's time to set the Enter key back to doing its normal
thing.

This seems like a rather risky approach, though. You can really frustrate
your users by messing with the basic functionality of the application.

On the other hand, April 1 is approaching...

macros - navigating
 

Jeff

That sub goes into the worksheet's module, not a standard module. In the
VBE's Project Explorer, you should have a module called ThisWorkbook, a
module for every sheet named Sheet1, Sheet2, etc. (unless you changed them)
and any standard or class modules you added. The sheet module that
corresponds to your form is the module for this code.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the
tab order doesn't mesh with left to right top to bottom on my form.
The link that has the change function looks like it would be perfect
but I couldn't get it to work. Here's the code from the link below. I
copied this into a module in my spreadsheet. It doesn't seem to
execute at all. Not sure if the module is wrong, or more likely how I
put it into my excel spreadsheet in the modules that is wrong. Any
additional assistance would be appreciated. Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)

'Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
'If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
'If the changed cell is the last array element
If i = UBound(aTabOrder) Then
'Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
'Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab
from unprotected cell to unprotected cell. Enter still doesn't
work like that, though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

or to get there a shorter way (IMHO) right mouse click on the sheet tab
where you want to use the code on and choose view code - you'll be in the
right place then.

Cheers
JulieD

"Dick Kusleika" wrote in message
...
Jeff

That sub goes into the worksheet's module, not a standard module. In the
VBE's Project Explorer, you should have a module called ThisWorkbook, a
module for every sheet named Sheet1, Sheet2, etc. (unless you changed
them) and any standard or class modules you added. The sheet module that
corresponds to your form is the module for this code.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the
tab order doesn't mesh with left to right top to bottom on my form.
The link that has the change function looks like it would be perfect
but I couldn't get it to work. Here's the code from the link below. I
copied this into a module in my spreadsheet. It doesn't seem to
execute at all. Not sure if the module is wrong, or more likely how I
put it into my excel spreadsheet in the modules that is wrong. Any
additional assistance would be appreciated. Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)

'Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
'If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
'If the changed cell is the last array element
If i = UBound(aTabOrder) Then
'Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
'Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab
from unprotected cell to unprotected cell. Enter still doesn't
work like that, though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

Good one, JulieD. Thanks.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

JulieD wrote:
or to get there a shorter way (IMHO) right mouse click on the sheet
tab where you want to use the code on and choose view code - you'll
be in the right place then.

Cheers
JulieD

"Dick Kusleika" wrote in message
...
Jeff

That sub goes into the worksheet's module, not a standard module. In the
VBE's Project Explorer, you should have a module called
ThisWorkbook, a module for every sheet named Sheet1, Sheet2, etc.
(unless you changed them) and any standard or class modules you
added. The sheet module that corresponds to your form is the module
for this code. --
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the
tab order doesn't mesh with left to right top to bottom on my form.
The link that has the change function looks like it would be perfect
but I couldn't get it to work. Here's the code from the link below.
I copied this into a module in my spreadsheet. It doesn't seem to
execute at all. Not sure if the module is wrong, or more likely how
I put it into my excel spreadsheet in the modules that is wrong. Any
additional assistance would be appreciated. Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)

'Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
'If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
'If the changed cell is the last array element
If i = UBound(aTabOrder) Then
'Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
'Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab
from unprotected cell to unprotected cell. Enter still doesn't
work like that, though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next
cell after I hit enter. For ex If i'm in cell a2 and I hit
enter I want my cursor to go to b5. After I hit enter when I'm
in b5 I want the cursor to go to c7 etc etc? Thanks
Jeff

macros - navigating
 

Thanks,

I got it to work. (Actually, you guys did).
Thanks
Jeff


"JulieD" wrote:

or to get there a shorter way (IMHO) right mouse click on the sheet tab
where you want to use the code on and choose view code - you'll be in the
right place then.

Cheers
JulieD

"Dick Kusleika" wrote in message
...
Jeff

That sub goes into the worksheet's module, not a standard module. In the
VBE's Project Explorer, you should have a module called ThisWorkbook, a
module for every sheet named Sheet1, Sheet2, etc. (unless you changed
them) and any standard or class modules you added. The sheet module that
corresponds to your form is the module for this code.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the
tab order doesn't mesh with left to right top to bottom on my form.
The link that has the change function looks like it would be perfect
but I couldn't get it to work. Here's the code from the link below. I
copied this into a module in my spreadsheet. It doesn't seem to
execute at all. Not sure if the module is wrong, or more likely how I
put it into my excel spreadsheet in the modules that is wrong. Any
additional assistance would be appreciated. Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)

'Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
'If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
'If the changed cell is the last array element
If i = UBound(aTabOrder) Then
'Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
'Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"Dick Kusleika" wrote:

Jeff

If you unlock those cells and protect the sheet, then you can tab
from unprotected cell to unprotected cell. Enter still doesn't
work like that, though.

If that isn't something you want to do, try this
http://www.dicks-blog.com/archives/2...rder-of-cells/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Jeff wrote:
How do I make a macro force my spreadsheet to jump to the next cell
after I hit enter. For ex If i'm in cell a2 and I hit enter I want
my cursor to go to b5. After I hit enter when I'm in b5 I want the
cursor to go to c7 etc etc? Thanks
Jeff