"sneaky" way to set tab order?

[Ron M.]

We have a rather complex spreadsheet for data collection. There are
about 50 data entry cells scattered all over it, and it would be a huge
help to be able to just hit <tab> to navigate from one data entry cell
to another.

This can be done, as you know, by selecting the cells in sequence while
holding down <control>, then entering a name for the range. Then the
user selects that name from the little pull-down menu to the upper left
of the worksheet. This activates the range with that name, and the
first cell in the series is highlighted and ready to go. No problem
there.

Some of our users aren't exactly rocket scientists, and I must make
this absolutely as simple as possible. What I want to do, is automate
this name/range-selection process, so when the user first opens the
worksheet, that range is already activated and ready to go, without
requiring the user to select it from that little menu in the upper
left.

So the whole thing would be transparent; the user would open the
worksheet, and it would open up all ready to start tabbing and entering
data, without the user having to do anything.

Surely there's some code I can stick in there to do this. Anybody?

Ron Morgan
Austin, Texas

[tony h]

ou need to select the range in he workboon_open method. then the code below will leave the range selected

Private Sub Workbook_Open()
Application.Goto Reference:="rMyEnterRangename"

End Sub


regards

[keepITcool]

maybe redesign your sheet:

make an "input area", where the user can enter all data
1 screen where he can see all "variables"
(use conditional formatting etc to point him to missing input..)

thus he doesnt need to navigate and on the printout it's simple to see
the pertinent "manual" data.

either move your original "scattered" input cells
to the new area, or simply put a reference to the new
input cell in the orignal input cell.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron M. wrote :

> We have a rather complex spreadsheet for data collection. There are
> about 50 data entry cells scattered all over it, and it would be a
> huge help to be able to just hit <tab> to navigate from one data
> entry cell to another.
>
> This can be done, as you know, by selecting the cells in sequence
> while holding down <control>, then entering a name for the range.
> Then the user selects that name from the little pull-down menu to the
> upper left of the worksheet. This activates the range with that name,
> and the first cell in the series is highlighted and ready to go. No
> problem there.
>
> Some of our users aren't exactly rocket scientists, and I must make
> this absolutely as simple as possible. What I want to do, is automate
> this name/range-selection process, so when the user first opens the
> worksheet, that range is already activated and ready to go, without
> requiring the user to select it from that little menu in the upper
> left.
>
> So the whole thing would be transparent; the user would open the
> worksheet, and it would open up all ready to start tabbing and
> entering data, without the user having to do anything.
>
> Surely there's some code I can stick in there to do this. Anybody?
>
> Ron Morgan
> Austin, Texas

[Rookie 1st class]

As long as I'm not the one writing it, consider Input Boxes for each cell
activated with a Workbook_Open function.
somthing similar to:
Public Sub Customer()
'Customer Name Macro
Range("CstmrNm").Select
Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The
customer name.", Title:="CUSTOMER NAME", Default:=Range("CstmrNm"))
'Customer Address Macro
Range("CstmrAddrss").Select
Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The
customer address." & Chr(13) & "" & Chr(13) & "PLEASE; LEAVE BLANK IF
CALIBRATION WAS PERFORMED AT OUR FACILITY.", Title:="CUSTOMER ADDRESS",
Default:=Range("CstmrAddrss"))
'Customer Item Number Macro
Range("CstmrItmNmbr").Select
Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The
number the customer assigned to this unit.", Title:="ITEM NUMBER",
Default:=Range("CstmrItmNmbr"))
End Sub

HTH Lou

"keepITcool" wrote:

>
>
> maybe redesign your sheet:
>
> make an "input area", where the user can enter all data
> 1 screen where he can see all "variables"
> (use conditional formatting etc to point him to missing input..)
>
> thus he doesnt need to navigate and on the printout it's simple to see
> the pertinent "manual" data.
>
> either move your original "scattered" input cells
> to the new area, or simply put a reference to the new
> input cell in the orignal input cell.
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Ron M. wrote :
>
> > We have a rather complex spreadsheet for data collection. There are
> > about 50 data entry cells scattered all over it, and it would be a
> > huge help to be able to just hit <tab> to navigate from one data
> > entry cell to another.
> >
> > This can be done, as you know, by selecting the cells in sequence
> > while holding down <control>, then entering a name for the range.
> > Then the user selects that name from the little pull-down menu to the
> > upper left of the worksheet. This activates the range with that name,
> > and the first cell in the series is highlighted and ready to go. No
> > problem there.
> >
> > Some of our users aren't exactly rocket scientists, and I must make
> > this absolutely as simple as possible. What I want to do, is automate
> > this name/range-selection process, so when the user first opens the
> > worksheet, that range is already activated and ready to go, without
> > requiring the user to select it from that little menu in the upper
> > left.
> >
> > So the whole thing would be transparent; the user would open the
> > worksheet, and it would open up all ready to start tabbing and
> > entering data, without the user having to do anything.
> >
> > Surely there's some code I can stick in there to do this. Anybody?
> >
> > Ron Morgan
> > Austin, Texas
>

[keepITcool]

Lou

a barrage of inputboxes is exactyl opposite to
my idea of a sensible way to interact with the user.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

[GS]

Possibly!

Can you unlock those input cells and protect the sheet. This will cause the
upper leftmost cell to be active (selected) when the workbook opens,
...usually. It depends where it was when last save, but you could add code to
Workbook_BeforeClose() to activate the starting cell so it's "ready-to-go"
when it opens.

Using just the Tab key will only allow navigation horizontally
left-to-right, and will proceed down to following rows when the there's no
more unlocked cell in a row.

If you don't need users to have access to non-input cells, they can only
follow the Tab key's route. It would be a good idea to review the design of
your sheet so this follows a logical order. In some cases, users may want to
use the other navigation keys in concert with the Tab key. Note that all
other navigation keys will also work 'normal' on protected sheets, but their
behavior can be dis-orienting if you're not used to using them with protected
sheets.

Regards,
GS