locked
VBA OnKey and OnTime Functions Will Not Run RRS feed

  • Question

  • I'm trying to get the OnKy and OnTime functions in VBA to run in an excel spreadsheet. I can't get them to execute and assume I don't have some control parameter set correctly. My test code is below:

    ----------------------------------------------------------------------------------

    '
    '  OnKey Test
    '
        Sub TestOnKey()
            Application.OnKey "{PgUp}", "OnKey_Test"
        End Sub
       
        Sub OnKey_Test()
            Beep
            MsgBox "Got OnKey Test"
        End Sub
    '
    '  Test OnTime script
    '
        Sub TestOnTime()
            Application.OnTime TimeValue("22:02:00"), "OnTime_Test"
        End Sub
       
        Sub OnTime_Test()
            Beep
            MsgBox "Got OnTime Test"
        End Sub

    ---------------------------------------------------------------------------------

    I have been able to get the test to work in debug mode.  However, when I save, exit and restart the spreadsheet it not longer works.  I don't get any error conditions it just will not run.

    Friday, October 24, 2014 11:06 PM

Answers

  • The assignments aren't stored with the workbook. You have to call TestOnKey and TestOnTime in the Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
        Call TestOnKey
        Call TestOnTime
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by George Hua Monday, November 3, 2014 3:08 AM
    Saturday, October 25, 2014 12:20 PM

All replies

  • The assignments aren't stored with the workbook. You have to call TestOnKey and TestOnTime in the Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
        Call TestOnKey
        Call TestOnTime
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by George Hua Monday, November 3, 2014 3:08 AM
    Saturday, October 25, 2014 12:20 PM
  • Hans, Thanks very much for your reply. It is now working and I'm very grateful.

    Saturday, October 25, 2014 11:39 PM