Application OnKey ctrl shift +

[AshleyKitsune]

Hi all, this is my first post here, but I've been a long time learner of vba. This is the first time I've ever tried to do something like this and I haven't been able to make it work, sadly.

I wanted to create a key combination that would add 1 to a cells value if it was a date. I also intend to do a subtraction method. After reviewing online for over an hour, I figured it was time to ask for help.

The key combination desired was Ctrl + shift + the Plus key (on the number pad)

The thing is - I want to be able to use this no matter what spreadsheet I have open, so I put it inside my personal.xlsb workbook's workbook tab.. is this not possible? Or do you think my company's policy settings are preventing me? Or am I just plain doing it wrong?

Thank you, Ashley.

Option Explicit
Private Sub Workbook_Activate()
    Application.OnKey "+^{+}", "AddDayToDate()"
End Sub
Private Sub Workbook_Deactivate()
    Application.OnKey "+^{+}"
End Sub

Sub AddDayToDate()
'
' AddDayToDate Macro
'

Dim myDate As Date

If IsDate(ActiveCell) Then
    myDate = ActiveCell.Value + 1
    ActiveCell.Value = myDate
    Else: MsgBox "Cell is not a date value."
End If
End Sub

 

[AshleyKitsune]

Quick note - I verified that the method "AddDayToDate" does work when executed.

 

[RoryA]

Use "^+{107}" for the key code.

 

[AshleyKitsune]

Use "^+{107}" for the key code.

Thank you for that. I updated the string, however it's still not running at all. There's no error. I've even closed the program to see if I needed to "activate" the page. And just now I tried using the even "Workbook_open" instead, with no results.

 

[Jaafar Tribak]

Remove the parentheses:

Application.OnKey "^+{107}", "AddDayToDate"

Are you executing that line using the Workbook_open of the personal workbook ?
And where is the AddDayToDate routine ? Is it in a separate standard module ?

See if this works.

Entire code goes in the ThisWorkbook Module:

Private Sub Workbook_Open()
    Application.OnKey "^+{107}", Me.CodeName & ".AddDayToDate"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^+{107}"
End Sub


Private Sub AddDayToDate()

    ' AddDayToDate Macro
    
    Dim myDate As Date
    
    If IsDate(ActiveCell) Then
        myDate = ActiveCell.Value + 1
        ActiveCell.Value = myDate
        Else: MsgBox "Cell is not a date value."
    End If

End Sub

 

[AshleyKitsune]

That did it, thank you! <3

 

[AshleyKitsune]

I discovered that you can use the constants vbKeyAdd and vbKeySubtract in place of the codes. With the add and subtract methods both working, this is the result:

Private Sub Workbook_Open()
    Application.OnKey "^+{" & vbKeyAdd & "}", Me.CodeName & ".AddDayToDate"
    Application.OnKey "^+{" & vbKeySubtract & "}", Me.CodeName & ".SubtractDayFromDate"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^+{" & vbKeyAdd & "}"
End Sub

Private Sub AddDayToDate()
    
    Dim myDate As Date
    
    If IsDate(ActiveCell) Then
        myDate = ActiveCell.Value + 1
        ActiveCell.Value = myDate
        Else: MsgBox "Cell is not a date value."
    End If

End Sub

Sub SubtractDayFromDate()

    Dim myDate As Date
    
    If IsDate(ActiveCell) Then
        myDate = ActiveCell.Value - 1
        ActiveCell.Value = myDate
        Else: MsgBox "Cell is not a date value."
    End If
End Sub