0

I am using Excel a lot for accounting. It happens a lot of time that I calculate multiple values into the same cell. So my cell may look like =12+345+23+88.

Now, the default Excel behavior is to delete the current content of a cell on a key press. And if you want to append some text, then you have to double-click or F2.

Is there a way I can set Excel to append on key press? For example, in my example above, I would press key + and Excel would open the cell for editing appending + symbol to the end.

1
  • This won't be a standard feature, F2 is as close as you'll get. There might be an add-in though. Sep 28, 2014 at 10:42

1 Answer 1

0

I have had a problem like yours some time ago, and I found a solution that you might be able to use too. The following is changed to fit your specific problem.

Put this in the sheet's code:

Private Sub Worksheet_Activate()

    Dim sht As Worksheet
    Set sht = ActiveWorkbook.Worksheets("Sheet1")

    If Not Intersect(ActiveCell, sht.Range("A1:A10")) Is Nothing Then

        ChangeKeys True

    End If

End Sub

Private Sub Worksheet_Deactivate()

    ChangeKeys False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim sht As Worksheet
    Set sht = ActiveWorkbook.Worksheets("Sheet1")

    If Not Intersect(ActiveCell, sht.Range("A1:A10")) Is Nothing Then

        ChangeKeys True

    Else

        ChangeKeys False

    End If

End Sub

The put this in a module:

Sub ChangeKeys(KeysChanged As Boolean)

    If KeysChanged Then

        'Add procedures to the keys

        'Functions on numpad (http://help.adobe.com/en_US/AS2LCR/Flash_10.0/help.html?content=00000520.html)
        Application.OnKey "{106}", "'KeyPress " & 42 & "'" 'Multiply *
        Application.OnKey "{107}", "'KeyPress " & 43 & "'" 'Add +
        Application.OnKey "{109}", "'KeyPress " & 45 & "'" 'Subtract -
        Application.OnKey "{111}", "'KeyPress " & 47 & "'" 'Divide /

    Else

        'Reset the keys

        'Functions on numpad (http://help.adobe.com/en_US/AS2LCR/Flash_10.0/help.html?content=00000520.html)
        Application.OnKey "{106}" 'Multiply *
        Application.OnKey "{107}" 'Add +
        Application.OnKey "{109}" 'Subtract -
        Application.OnKey "{111}" 'Divide /

    End If

End Sub


Function KeyPress(the_key As Long)

    Application.SendKeys "{F2}{" & Chr$(the_key) & "}"

End Function

The keypress behaviour is changed for "+ - * /" (although it does not seem to work for "/" in Excel 2013) when you select a cell in Range("A1:A10") and changed back when you select a cell outside this range or activates another sheet. You might want to do something similar when you deactivate/activate the entire workbook.

Best regards,

Simon

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .