Moving within a form

[Cindi]

I am trying to creat a form so that when the person filling it out completes
a cell, I can direct which cell they go to next. Is that possible?

I have locked the cells they cannot change, but tabbing through it, I get
stuck in a circle in a couple of places.

 

[john]

'try Anne Troy's taborder event code it may do what you you require.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "D5", "C5", "A10", "D10", "C10")

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

End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module. Adjust the aTabOrd Array to suit your need.

Alt + q to go back to the Excel window.

Enter data in A5 press Enter & you will be taken to the next cell.in the
aTabOrd

Hope useful
credit to Anne Troy.

 

[Otto Moehrbach]

Cindi
A macro like the one below will do what you want. You will have to fill
in the macro for all your data entry cells. Here's how:
Look at the 2 lines :
Case "A1": Range("C3").Select
Case "G2": Range("H5").Select
This says if the user enters something in A1, the active cell will jump to
C3. If he enters something in G2, the active cell jumps to H5. Change
these as needed, then add as many similar lines as you need.
Also, as written, you have to name the range of all the data entry cells. I
chose the name "AllEntryCells". Change this as you wish. Post back if you
need more.
This is a sheet event macro and must be placed in the sheet module of your
sheet. To access that module, right-click on the sheet tab and select View
Code. Paste this macro into that module. "X" out of the module to return
to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("AllEntryCells")) Is Nothing Then
Select Case Target.Address(0, 0)
Case "A1": Range("C3").Select
Case "G2": Range("H5").Select
'Etc
'Etc
'Etc
End Select
End If
End Sub

 

[Cindi]

Ok, this is way more complicated than I can understand. Thanks anyway.

 

[Rogerbid]

Hi Otto, I came to this post while seeking help with a similar problem. I
believe your macro will assist me but I have pasted it in as instructed and
every time I try to run it I get an error at the line:

If Not Intersect(Target, Range("AllEntryCells")) Is Nothing Then

The error is

Run-time error '1004', Method 'Range' of object'_Worksheet' failed

I have no idea what this means or what to do to fix it. If you have a
moment to reply I would be most greateful,

Thanks,

Roger

 

[Rogerbid]

Hi John,

I have tried Anne Troy's code and find it is conditional on the content of a
cell being changed by the user, so if one wants to pass by a cell without
changing it, the default tab order takes over. Is there a way to change this
please so that the chosen tab order is followed regardless?

Thanks,

Best wishes, Roger

 

[Otto Moehrbach]

Roger
Do you have a range named "AllEntryCells"? As written, that range name
is required. Tell me more about what you have and what you want to do. HTH
Otto

 

[Rogerbid]

Hello Otto,

Thanks so much for the quick reply. I am not at the office today, and not
back until Tuesday, but can answer your question without having the file
infront of me.

Sadly my knowledge is very limited and in a word, No, I do not have a "range
named "AllEntryCells"! I simply copied the code and hoped forthe best. I
did read the bit "you have to name the range of all the data entry cells" and
the significance did not register, sorry.

If it is a fairly simple task to describe and if you have the time, then I
would be grateful for help, or maybe you can simply post a link to a site
where I can learn this.

I am sorry to trouble you, but appreciate anything you can offer in the way
of advice since this seems to be the closest I have come to finding a
solution to the task I have before me!

Thanks again,

Roger

 

[Otto Moehrbach]

If your data and problem are similar to what Cindi had, as you said it is,
then I can furnish some explanation. Basically, she has a bunch of cells
into which a user will be entering data. Since these cells are in a Form
arrangement, the data entry cells are not contiguous. What she wanted is
for the active cell to move to a specific other data entry cell when an
entry is made in a data entry cell.
The macro that I chose for this is named a Worksheet_Change macro. That is
a macro that fires automatically whenever a change is made to the contents
of ANY cell in the entire sheet. But she wants this macro to work for her
when a change is made to ONLY her group of data entry cells. So the macro
has to be written that way. That is accomplished by the statement:
If Not Intersect(Target, Range("AllEntryCells")) Is Nothing Then
In English, this statement says "Do the following steps IF the target cell
(the cell that changed) is in a specific group of cells". That means that
Excel must have a means of knowing what that specific group of cells is.
That is done by naming that group of cells, then using that name in the
above statement. That's where "AllEntryCells" comes from. I chose that
name out of the blue. You can name it Doodle or anything else you want.
Just be sure to substitute the name you choose for "AllEntryCells" in the
above line of code.
If you wish, contact me via email and either send me an explanation of
what you have and want or send me the file or both. My email address is
(e-mail address removed). Remove the "extra" from this address. Otto

 

[Rogerbid]

Hi Otto,

My goodness, thanks for your very long reply! I am sorry to involve you in
so much work.

Your interpretation of my problem is quite correct and on Tuesday I will be
pleased to accept your offer and email you my file. I do appreciate your
offer of help with this problem.

With best wishes,

Roger