Can you assign an "order" for cell entry?

T

TheMilkGuy

Hi folks,

I have a sheet with a couple dozen input cells spread out over the
screen.

Is there a way to assign an order to them so that when a value is
entered and the Return key hit, the cell advances to the next chosen
cell?

I am sure there is a phrase for what I am feebly trying to describe...

Thanks,
Craig
 
R

Rick Rothstein \(MVP - VB\)

This may do what you want. Right-click the worksheet tab (at the bottom of
the page) for the worksheet you want this functionality on and then select
View Code from the popup window. Copy/paste the following code into the code
window that opens up when you do that...

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
If InStr(" " & Addr & " ", " " & Target.Address(False, False) & " ") Then
Range(Split(Split(Addr & " " & Target.Address(False, False), _
Target.Address(False, False) & " ")(1))(0)).Select
End If
End Sub

Simply put the list of cell address (space delimited), in the order you want
them processed in, into the "Const Addr" statement in place of the sample
addresses I used. Now, go back to the worksheet, click in one of those cell
and edit it... pressing Return or Tab should take you to the next cell in
the list. You didn't say what you wanted to happen, so after the last cell
in the list is edited, that cell will remain the active cell after Return or
Tab are pressed. If you have a natural "parking area" for your active cell,
just put it at the end of the list.

Rick
 
R

Rick Rothstein \(MVP - VB\)

Actually, with the following code, the movement out of the last cell will be
normal (that is, it will be in accordance with your "Move selection after
Enter" option on Tools/Options/Edit(tab) from Excel's menu bar.

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
With Target
If InStr(" " & Addr & " ", " " & .Address(False, False) & " ") Then
If .Address(False, False) <> Mid$(Addr, InStrRev(Addr, " ") + 1) Then
Range(Split(Split(Addr & " " & .Address(False, False), _
.Address(False, False) & " ")(1))(0)).Select
End If
End If
End With
End Sub

Rick
 
T

TheMilkGuy

Rick,

Sorry, but I'm using Excel 2007 and I don't have a Tools menu...

Otherwise, I followed your instructions and inserted the code, but it
didn't seem to do anything at all... Am I missing a step? Sorry, but
you may have to dumb-it-down even further for me. :p

Many thanks,
Craig
 
R

Rick Rothstein \(MVP - VB\)

First off, for future questions you might ask in these newsgroups... tell us
you are using XL2007 when you post your question (it could make a difference
in the answer you will need).

Okay, I fired up my copy of XL2007 and what I posted works there (as I would
have expected), so lets see if we can get you up to speed on how to use what
I posted. If you don't already have it running, start up your copy of XL2007
and go to a blank worksheet. Just to repeat, right-click the tab at the
bottom of the worksheet (the tab will probably have SheetX as its caption
where X is a number), select View Code from the popup menu that appears and
copy/paste this code (don't change it) into the window that came up inside
the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
With Target
If InStr(" " & Addr & " ", " " & .Address(False, False) & " ") Then
If .Address(False, False) <> Mid$(Addr, InStrRev(Addr, " ") + 1) Then
Range(Split(Split(Addr & " " & .Address(False, False), _
.Address(False, False) & " ")(1))(0)).Select
End If
End If
End With
End Sub

Now, go back to the worksheet whose tab you clicked in order to copy/paste
the code above. Click into cell D1. Type something and then press the Enter
key. G10 should have automatically become the active cell. Type something
into it and press the Enter key. Now A5 should have automatically become the
active cell. Type something into it and this time press the Tab key. Now B7
should have become the active cell. Do this once more and you should be in
cell C3. Since this the last cell in the list assigned to the Const Addr
statement, typing something in it and pressing Enter or Tab will move the
active cell highlight in whatever direction your option is set for. This
option can be found by clicking the Office button (the large round circle
icon in the upper left corner), clicking the Excel Options button at the
bottom right corner of the dialog box that came up, clicking Advanced in the
listing on the left of the options' dialog box and looking at the first item
in the "Editing options" section in the panel on the right.

Rick


Rick,

Sorry, but I'm using Excel 2007 and I don't have a Tools menu...

Otherwise, I followed your instructions and inserted the code, but it
didn't seem to do anything at all... Am I missing a step? Sorry, but
you may have to dumb-it-down even further for me. :p

Many thanks,
Craig
 
T

TheMilkGuy

Rick,

Sorry for leaving out the version info - did manage to find the
Advanced Options after my brief panic and post...

Your code does work fine... Completely my fault, too (shock and awe!)
- seems I thought I could just add D1 to the end of the Const Addr
list and create a 'loop'. Does my VB inexperience show? :p

Thanks for the aid and the patience. If there is an easy way to loop
that code, I'd be interested to hear it.

Again, many thanks!
Craig
 
R

Rick Rothstein \(MVP - VB\)

If by "loop the code" you mean return to the first cell in the list after
editing the last listed cell, try this code...

Private Sub Worksheet_Change(ByVal Target As Range)
Const Addr = "D1 G10 A5 B7 C3"
With Target
If InStr(" " & Addr & " ", " " & .Address(False, False) & " ") Then
If .Address(False, False) <> Mid$(Addr, InStrRev(Addr, " ") + 1) Then
Range(Split(Split(Addr & " " & .Address(False, False), _
.Address(False, False) & " ")(1))(0)).Select
ElseIf .Address(False, False) = Split(Addr)(UBound(Split(Addr))) Then
Range(Split(Addr)(0)).Select
End If
End If
End With
End Sub

Rick


Rick,

Sorry for leaving out the version info - did manage to find the
Advanced Options after my brief panic and post...

Your code does work fine... Completely my fault, too (shock and awe!)
- seems I thought I could just add D1 to the end of the Const Addr
list and create a 'loop'. Does my VB inexperience show? :p

Thanks for the aid and the patience. If there is an easy way to loop
that code, I'd be interested to hear it.

Again, many thanks!
Craig
 
T

TheMilkGuy

Rick,

Works like a charm! Thank you very much for your help (and patience!)

All the best,
Craig
 
Top