macros - navigating

[Guest]

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

 

[Dick Kusleika]

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/2004/07/06/tab-order-of-cells/

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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...

 

[Dick Kusleika]

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.

 

[JulieD]

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]

Good one, JulieD. Thanks.

 

[Guest]

Thanks,

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