none
Open Event using SendKeys fails when opened from a VBA procedure or VBE RRS feed

  • Question

  • Using Excel in Office 2016 and Windows 10.

    I have a one worksheet workbook with a custom tab on the ribbon.  The tab shows the letters "Y1" when Alt is pressed with the sheet active.  I wish to make that ribbon custom tab active when I open the workbook, and I have achieved that with the following code in the Workbook Open event:

    Private Sub Workbook_Open()
        Application.SendKeys "%Y1%"
    End Sub

    When I open the file from Windows explorer by double clicking, it opens without a problem with the custom ribbon tab active, though I notice a very short (about 1/4 second) pause before it changes from the Home tab to the custom one.

    However,  When I open the file from a VBA procedure or by typing into the Immediate window:

    Workbooks.Open ("C:\Data2\ColtarAddressesAndData.xlsm")

    the file opens OK. but does not change from the ribbon Home tab to my new custom tab.  Furthermore it gives me a "Ding" sound and inserts a "1" into the next line down in the immediate window (or into the VBE if I am stepping through with F8).

    I have tried adding a "Wait" of up to 3 seconds in the Open Event code before the SendKeys line to no avail.  I have tried moving the instruction to the Worksheet Activate event and then re-activating the sheet in the code.  I have tried various combinations of spaces and "&" symbols between the % (for Alt) in the Open Event code. 

    The new tab is the second one on the ribbon, immediately after the Home tab.  I suppose that I might try using the XML to move it to position to the left of the Home tab and see if that works, but I would prefer to leave the Home tab as the first one after the "File".

    Any suggestions would be gratefully received.

    Andy C

    Sunday, September 25, 2016 2:50 PM

Answers

  • Try this:

    1) In a standard module, create the following macro:

    Sub ShowMyTab()
        Application.SendKeys "%Y1%"
    End Sub

    2) Change the Workbook_Open event procedure to

    Private Sub Workbook_Open()
        Application.OnTime Now + TimeSerial(0, 0, 1), "ShowMyTab"
    End Sub


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

    • Marked as answer by AndyColRomsey Sunday, September 25, 2016 3:58 PM
    Sunday, September 25, 2016 3:53 PM

All replies

  • Try this:

    1) In a standard module, create the following macro:

    Sub ShowMyTab()
        Application.SendKeys "%Y1%"
    End Sub

    2) Change the Workbook_Open event procedure to

    Private Sub Workbook_Open()
        Application.OnTime Now + TimeSerial(0, 0, 1), "ShowMyTab"
    End Sub


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

    • Marked as answer by AndyColRomsey Sunday, September 25, 2016 3:58 PM
    Sunday, September 25, 2016 3:53 PM
  • Hans

    Sheer Genius!  It worked as required.  Many thanks

    Andy C

    Sunday, September 25, 2016 4:00 PM
  • Thank you for this code!  I didn't know to use Application.OnTime, which worked!  I had been trying to use Application.Wait before calling my sub with no success.

    Best Regards,

    Amy Putnam

    Tuesday, August 22, 2017 7:59 PM