tabbing thru a form to enter data

[wilcox96]

i've created a form that i want users to hit "enter" or "tab" and it advances to the next cell for entry. the cells are not all aligned or in order.

"without" using vba, is there a way to accomplish this? I have tried (I believe it was )CTRL enter...to select these cells. However it is too volatile as the sequence can be wiped out if the user gets off track or hits any key other than enter.

Thanks for any assistance you can provide...

 

[wilcox96]

Like I said before...I am dangerous enough to figure out how to get that stuff in there.

I did all this stuff before. It doesn't seem to "follow along". Anyway.... maybe if i understood VBA I would recognize what is slightly off in the formula...(from pasting or otherwise..).

It's not a "huge" deal... They can up/down/left/right arrow to wherever they need to anyway..

Thank you, Erik!!!

 

[erik.van.geit]

Hey,

Do you say it didn't work for you and you give up ?
You could tell what's not working ... Just a proposal respecting your choice :wink:

kind regards,
Erik

 

[wilcox96]

okay, okay.... I am not a quitter...ha. I've just been working on this for a good while... got it doing everything but enter the information for them... (don't start...haha.). Okay...might as well continue. (I keep saying "this is the last thing I need it to do.....then, there's another).

What do I look for? (what happens is...it actually followed along for the first few cells...then, got off...kept going down cells. I tried to press tab, enter, anything...but it just did not rectify. I copied everything from your previous formula "except" the part with your name. is that where I did wrong? I figured that wasn't truly part of any function... so....??)

where do we go from here...Erik the task master?

 

[erik.van.geit]

Private Sub Worksheet_Change(ByVal Target As Range)

It only goes to the next cell in the range when "Target" has been changed. It is not tabbing when you just hit "enter" different times.

therefore try this too
it will only allow selections within the array
if you select a cell outside the array, the last previously selected "allowed cell" will be activated again

I can see occur some other problems to fix, but let's first look to this part ...

are you getting further ?
kind regards,
Erik

Option Explicit

Public previous_target As Range
Public taborder As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'050318 2338
Dim i As Long
Application.EnableEvents = False
set_taborder
i = Application.WorksheetFunction.Match(Target.Address(0, 0), taborder, 0) - 1

If i < UBound(taborder) Then Range(taborder(i + 1)).Select Else Range(taborder(0)).Select

Set previous_target = Selection
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
set_taborder
i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(Target.Address(0, 0), taborder, 0) - 1
On Error GoTo 0
  If i = -1 Then
  i = -1
  On Error Resume Next
  i = Application.WorksheetFunction.Match(previous_target.Address(0, 0), taborder, 0) - 1
  On Error GoTo 0
  If i = -1 Then Range(taborder(0)).Select Else previous_target.Select
  End If
End Sub

Private Sub set_taborder()
taborder = Array("C6", "C7", "C8", "C9", "C10", "H5", "H6", "H7", "H8", "H9", "H10", _
"H11", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "C13", "L13", "S13")
End Sub

kind regards,
Erik

 

[wilcox96]

OKAY...just now able to get back to this...
Put the code on sheet 1...but it gives error message. ran debug.. stops at the following:

i = Application.WorksheetFunction.Match(Target.Address(0, 0), taborder, 0) - 1

i didn't go any further with debug than that. any thoughts?

thanks!

 

[erik.van.geit]

open a new workbook and
1. Select the sheet you want code to work in
2. right click the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

click in cell C6
type something
ENTER

you would to to cell C7

repeat typing + ENTER ...

finally you'll get back to C6

does that work ?

kind regards,
Erik

 

[wilcox96]

yeah...i did all that before. (i did it "again" just to make sure). here's what happens....

i type in something in C6...hit enter...and it comes right back to C6 and won't get off it. even if i click another cell somewhere...it jumps back to C6.

what do you think?

 

[erik.van.geit]

what do you think?

we'll find a solution, that's what I think !
going back to C6 is caused by the selection_change event

if you select a cell which doesn't belong to the array the code moves you to the cell where you should be

why is that code triggered in the wrong way (or it seems to do)
can you post your entire code please ?
confirm also if there are no other sheet or workbook codes

if you don't find a solution and your code is exactly the same as the one here, then you may email your workbook if you want

kind regards,
Erik