Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - trap TAB key (Excel 2000+)

    A data entry worksheet. The user keys data into two groups of cells in a row.
    The first set of cells runs from columns "E" through "I" (hence tests for 5 & 6 in my VBA code)
    The second set of cells runs from columns "O" through "V".

    My idea: The user changes the value in the first of the five cells, column "E", and taps the Enter key.
    I detect this and automatically select the next 4 cells to facilitate data-entry with the Enter key.

    The User's Idea: They use the TAB key rather than the Enter key.

    My code now finds that the Activecell is in column 6, assumes that the user has now completed the five columns of data entry, and shoots off to the second set of cells.

    I have a feeling that I'd like to be able to ask, "So, you are in column6. Did you arrive here by the Enter key - in which case I will select cells, otherwise I'll bow out gracefully".
    How would I set about determining the "key just used"?



    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ''' #2950
    If ActiveSheet.Name = strcDollarExpensesSheet Then
    If ActiveCell.Column = 5 Then
    ActiveCell.Range(Cells(1, 2), Cells(1, 5)).Select
    Else
    If ActiveCell.Column = 6 Then
    ActiveCell.Range(Cells(1, 10), Cells(1, 17)).Select
    Else
    End If
    End If
    Else
    End If
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel - trap TAB key (Excel 2000+)

    1) Why do you use the SheetChange event of the workbook for this? Wouldn't it be easier to use the Change event of the appropriate sheet?

    2) I don't think you can determine whether the user pressed Tab or Enter. But you could keep track of how the user moves through the worksheet by using the SelectionChange event of the worksheet and storing the Target range or its address in a module-level or global variable CurrentTarget, and before you do so, transfer the value of CurrentTarget to another variable PreviousTarget.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - trap TAB key (Excel 2000+)

    1) Why do you use the SheetChange event of the workbook for this? Wouldn't it be easier to use the Change event of the appropriate sheet?
    Because I wasn't thinking when I drafted the trial code. Now changed. Thank you!

    2) ... transfer the value of CurrentTarget to another variable PreviousTarget.
    Agreed. But I was hoping to avoid work (grin!).
    I think I'll demo the existing code, then disable it for the first live run, and work on it some more.

    Thanks, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •