Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incrementing Cell Values on +/- Key Press (Excel 2

    I'm lost. I can kind of muddle my way through VBA with Word, but I just don't "get" Excel.

    I'm trying to trap a keypress and increment the value of the adjacent cell (or current cell if that will work) based on whether + or - is pressed. I'm figuring this needs something to do with the Change event and then Offset to get the correct cell. Here's what I'm after (in something not resembling real VBA/Excel functions at all)...

    Use mouse or keyboard to move cursor to any cell in column D
    Press either + or - key on keypad
    If Key = - Then CellValue in same row in column C = CellValue - 1
    If Key = + Then CellValue = CellValue + 1

    Can anyone point me in the right direction?

    Thanks,
    -Steve

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Cell Values on +/- Key Press (Exc

    Hi Steve, welcome to Woody's Lounge.

    I don't think (Hans may correct me) that you can trap a keypress in Excel the way you want to without using a form which has the focus. The reason is that the Change event only fires AFTER you press Enter; ie after the value of a cell has actually been changed.

    If you paste the following code in the VB Editor for the worksheet in question you can see what I mean

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Intersect(Range("D"), Target)
    If r Is Nothing Then
    Else
    If Target = "+" Then
    Target = ""
    Target.Offset(0, -1) = Target.Offset(0, -1) + 1
    End If
    If Target = "-" Then
    Target = ""
    Target.Offset(0, -1) = Target.Offset(0, -1) - 1
    End If
    Target.Select
    End If
    End Sub

    This will do what you want, but you have to press return.

    The other option would be to bring up a form when you select a cell in column D, and through this use code to change the values depending on pressing '+' or '-', then have another keypress to close the form - say Escape?
    Thanks,

    pmatz

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

    Re: Incrementing Cell Values on +/- Key Press (Excel 2

    You can use a combination of workbook and worksheet events and OnKey to do this, but I'm not sure it's worth the trouble. The use of event handlers will disable Undo in the worksheet - running VBA code always clears the undo buffer.

    See the attached workbook.
    Module 1 contains macros that set and clear key assignments for + and - both on the standard keyboard and on the numeric keypad.
    The worksheet module for Sheet1 contains event handlers for the Activate, Deactivate and SelectionChange events of the worksheet.
    The workbook module ThisWorkbook contains event handlers for the Activate and Deactivate events of the workbook.

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

    Re: Incrementing Cell Values on +/- Key Press (Excel 2

    Come to think of it, it might be more intuitive to select a cell in column C itself and use + and - to increase/decrease the value. See attached version.
    You can still enter + and - while editing the cell. The only gotcha it that you must press F2 before starting to enter a negative number.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Cell Values on +/- Key Press (Exc

    Hans, as always - I'm very impressed! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> (Will need to study that code myself)
    Thanks,

    pmatz

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Cell Values on +/- Key Press (Exc

    Perfect! Thanks a lot.

    I agree that doing it in the same cell is more intuitive. I just couldn't figure out how to trap the keypress before it overwrote what was in the cell.

    Thanks again,
    -Steve

Posting Permissions

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