Single-character entry in cell --> Move to next cell down

[discgolfdc]

Hello, all. I'm hoping that I might be able to get a (somewhat) quick answer here. I'm relatively new with the whole VBA thing in Excel, and have managed to pull off one or two simple things with some help. I'll likely never be a Microsoft MVP or a VBA expert, and that's why I appreciate these boards.

I have designed a simple little spreadsheet that allows me to compare a student's answers to test questions against those that would be considered the "key." What I am trying to achieve, however, is the ability to have Excel select the cell immediately below the one I'm currently in (without having to hit enter, tab, down arrow, etc.) once I enter a letter in the current cell. I do have the worksheet protected, but as it stands, I still have to hit one of those keys to get to the next cell down. If I could get Excel to do the "Enter/Tab/Down Arrow" thing FOR me upon entry of a single character, I'd be able to really fly through grading, as it would literally be a single-hand operation. Turning pages with one hand, hitting "A", "B", "C", "D", etc., with the other -- now that's efficient!!

The student's answers are entered in cells C4 through C78. Specifically, I'd like to be able to start in Cell C4, press the letter corresponding to the student's answer, and have Excel jump down to Cell C5 immediately after I've hit that letter key. I'd like to be able to continue this pattern so that when I wind up in Cell C78, that immediate jump no longer occurs (there'd be nowhere else for it to go at that point, being that that is the last question).

Is there a way to get Excel to move down a cell upon entry of a single character of data in a particular cell? I appreciate any and all feedback.


With admiration of all you VBA folks out there...I don't know how you learn all of what you learn...

Jayson

 

[Jonmo1]

Welcome to the board...

Unfortunately, you ask the impossible.

Once you start typing in a cell, you are in Edit mode.
No macro or anthing else will run while you are in edit mode.
You have to press ether Enter/Tab/ArrowKey, or even click out of the cell with your mouse before any macro code can continue or start to run.

Sorry...


It is however possible with Text Boxes...

If you want to use Textboxes from the "Tools - Control Toolbox" menu...

 

[discgolfdc]

Not even with these things I'm reading about called "Worksheet Change Events"?

I am not sure how those work, but if they're subject to the same macro rule you mention, I guess they wouldn't.

 

[Boller]

Here's one way (although not exactly the way you wanted).

Assuming that cell C1 can be used as an input cell, select C1 and press the keyboard letters for the grades.
When all of the letters have been input to C1 press enter and the grades will be entered in C4:C78

Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputCell As Range, r&
Set inputCell = [C1]
If Not Intersect(Target, inputCell) Is Nothing Then
    Application.EnableEvents = False
    For r = 4 To 78
        Cells(r, 3) = Mid(inputCell, r - 3, 1)
    Next
    inputCell.ClearContents
    Application.EnableEvents = True
End If
End Sub

This can be refined, if required, to allow for partial input before entering in C1.

 

[discgolfdc]

Here's one way (although not exactly the way you wanted).

Assuming that cell C1 can be used as an input cell, select C1 and press the keyboard letters for the grades.
When all of the letters have been input to C1 press enter and the grades will be entered in C4:C78

Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputCell As Range, r&
Set inputCell = [C1]
If Not Intersect(Target, inputCell) Is Nothing Then
    Application.EnableEvents = False
    For r = 4 To 78
        Cells(r, 3) = Mid(inputCell, r - 3, 1)
    Next
    inputCell.ClearContents
    Application.EnableEvents = True
End If
End Sub

This can be refined, if required, to allow for partial input before entering in C1.

I think I see where you're going with this, and, frankly, if it somehow works, it matters not to me how those letters get into their respective cells.

Will this work if the input cell is A2? I simply hit Alt-F11, copied the code into the VBA editor, and changed the "C1" in your original post to "A2". When I went back to the sheet and entered 75 letters in a row in cell A2 and hit Enter, it did nothing. Am I missing something?

Again, thanks for your time.

 

[lenze]

You need to place the code in the WorkSheet module. Right click the sheet tab and choose "View Code"
lenze

 

[discgolfdc]

Many thanks to all. It works like a charm.

 

[dUBBINS]

I am no expert, and this is a very old thread, but I do not understand why it is not possible to do exactly what is asked. If anyone runs into this problem, try the Application.OnKey method to solve this. It would take the tedious process of setting up 27 subs; one main one, and one for each letter of the alphabet (or how ever many letters are needed). Each sub could print the depressed letter and offset to the next cell down. Just would have to start the main sub each time, but you could add a onscreen button to make that easy as well.