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

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Incrementing Cell Values on +/- Key Press (Excel 2

    Author
    Topic
    #422919

    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

    Viewing 2 reply threads
    Author
    Replies
    • #965981

      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: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?

    • #965983

      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.

    • #966003

      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.

      • #966026

        Hans, as always – I’m very impressed! bravo (Will need to study that code myself)

      • #966311

        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

    Viewing 2 reply threads
    Reply To: Incrementing Cell Values on +/- Key Press (Excel 2

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: