Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
I have a template that I have created that is to be used for data entry. I
have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
I'll be watching this one - it's related to a feature I'd like to use also.
But the problem is that I don't know of a way of trapping a character as it's typed into a cell. The Worksheet_Change() event would seem the place to look for this type of thing, but the _Change() event doesn't trigger until you hit that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until you (also) change the selected cell. "crowzhome" wrote: I have a template that I have created that is to be used for data entry. I have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
I know, it is driving me crazy. I was hoping it would be in the validation
option when I defined the field with one character. Too bad there isn't a check box that states move to next unlocked cell. I guess that would be too simple. If you find anything out, please let me know. "JLatham" wrote: I'll be watching this one - it's related to a feature I'd like to use also. But the problem is that I don't know of a way of trapping a character as it's typed into a cell. The Worksheet_Change() event would seem the place to look for this type of thing, but the _Change() event doesn't trigger until you hit that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until you (also) change the selected cell. "crowzhome" wrote: I have a template that I have created that is to be used for data entry. I have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
Hi,
This is not a very elegant way of doing it and I await nicer solutions: In the worksheet (or workbook) code sheet paste the following. Sub Worksheet_Activate() Application.OnKey "A", "Move_A" Application.OnKey "a", "Move_a" Application.OnKey "B", "Move_B" '€¦. For every key!!!!€¦. End Sub Sub Worksheet_Deactivate() Application.OnKey "A" End Sub Thes are in a seperate module (can't get it to work in the worksheet code) paste the following: Sub MoveR(c as string) Application.EnableEvents = False ActiveCell = c ActiveCell.Offset(0, 1).Activate Application.EnableEvents = True End Sub Sub Move_A() MoveR("A") End Sub Sub Move_a() MoveR("A") End Sub Sub Move_B() MoveR("A") End Sub There needs to be some tidying up and careful reviewing of the events you may also need to check the target address to ensure you only deal with the cells that require special treatment. I watch with interest on improvements on getting the keystroke or dynamically calling a macro with onkey (not worked for me as in application.onkey "mover(""A"")" -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "crowzhome" wrote: I have a template that I have created that is to be used for data entry. I have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
Would it be a solution to have a separate input cell, where you would input
the name normally (typing continuously, then ENTER)? The one-letter cells, where you want to end up each character of the entered name would contain formulas to extract the characters. Data validation is not required anymore. Example: Input cell is A1. Cells B1, C2.... contain the fomulas =MID(A1,1,1) , =MID(A1,2,1) etc. Cheers, Joerg Mochikun "Martin Fishlock" wrote in message ... Hi, This is not a very elegant way of doing it and I await nicer solutions: In the worksheet (or workbook) code sheet paste the following. Sub Worksheet_Activate() Application.OnKey "A", "Move_A" Application.OnKey "a", "Move_a" Application.OnKey "B", "Move_B" '$B!D(B. For every key!!!!$B!D(B. End Sub Sub Worksheet_Deactivate() Application.OnKey "A" End Sub Thes are in a seperate module (can't get it to work in the worksheet code) paste the following: Sub MoveR(c as string) Application.EnableEvents = False ActiveCell = c ActiveCell.Offset(0, 1).Activate Application.EnableEvents = True End Sub Sub Move_A() MoveR("A") End Sub Sub Move_a() MoveR("A") End Sub Sub Move_B() MoveR("A") End Sub There needs to be some tidying up and careful reviewing of the events you may also need to check the target address to ensure you only deal with the cells that require special treatment. I watch with interest on improvements on getting the keystroke or dynamically calling a macro with onkey (not worked for me as in application.onkey "mover(""A"")" -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "crowzhome" wrote: I have a template that I have created that is to be used for data entry. I have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I advance to the next field automatically?
It looks to me (without testing) that Martin Fishlock's solution could work
for you. I hadn't thought of using the OnKey. As he said, it's not elegant but could probably work. Only problem I can think of right away is when you get to the end of the row or if there are blank cells to be left between "fields". But you could actually use his suggestion combined with Application.Intersect to determine which column you are in and adjust the forced moves to next cell to skip columns or bounce down to the next row. My own initial thoughts were more like Joerg (and Martin elaborated on) - either have a cell or form to type the entire entry into and then have a little VBA code to break the entries out into the individual cells on the worksheet. I didn't suggest the cell method because I figured you didn't want to clutter up your form with added cells that you need to deal with. But it would be one way to go. "crowzhome" wrote: I know, it is driving me crazy. I was hoping it would be in the validation option when I defined the field with one character. Too bad there isn't a check box that states move to next unlocked cell. I guess that would be too simple. If you find anything out, please let me know. "JLatham" wrote: I'll be watching this one - it's related to a feature I'd like to use also. But the problem is that I don't know of a way of trapping a character as it's typed into a cell. The Worksheet_Change() event would seem the place to look for this type of thing, but the _Change() event doesn't trigger until you hit that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until you (also) change the selected cell. "crowzhome" wrote: I have a template that I have created that is to be used for data entry. I have a validation on each cell that allows no more than one character. What I would like happen is once that character is entered, it automatically goes to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys. For example, for the last name, there is a field for each alpha of the last name and right now I have to enter each alpa and then tab to the next field and type in the second letter of the last name and then so on and so on. I just want to be able to type continuously and have each alpha placed in the corresponding column. I know there has to be a way. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to automatically advance a number when printing? | Excel Worksheet Functions | |||
Display the source for a pivot table page field | Excel Worksheet Functions | |||
pivot chart is it possible to hide individual field buttons? Layout Field buttons | Charts and Charting in Excel | |||
Conditional Formatting of Pivot Table Field | Excel Discussion (Misc queries) | |||
how can i automatically insert cell values into a comment field? | Excel Discussion (Misc queries) |