locked
Excel 2010 bug: 'SendKeys "^{HOME}", True' -- VBA fails to run all lines of code after this statement RRS feed

  • Question

  • Hello.

    In the past few weeks I've noticed that the VBA statement...

    SendKeys "^{HOME}", True


    ... no longer works properly in Excel 2010.

    I've built data entry templates that have macros for resizing columns back to specified defaults (i.e., "house-cleaning" macros after people use the templates). The SendKeys statement above no longer works when used in procedures that have lines of code running other statements. Lines of code listed below SendKeys are not executed in such procedures.

    For example:

    Public Sub ResetAllColumnWidths()
    ' Restores all columns on site, sample and analysis results sheets
    '
    
        Application.ScreenUpdating = False
        
        ResetColWidths_Sites
        ResetColWidths_GaugeWaterElevation
        ResetColWidths_BenchmarkWaterElevation
        ResetColWidths_ShorelineObservations
        
        Application.ScreenUpdating = True
        Worksheets("File Info").Activate
        SendKeys "^{HOME}", True    '<--- from this point on, lines of code below are not executed
        Range("Project").Select
    
    End Sub

    • Has anyone else seen this bug?
    • Is this an actual bug in Excel 2010?
    • Is there an alternative, or workaround?
    • Or, is there something else I'm missing here, such as an Excel setting?

    I am using Excel 2010 in Windows 7 Pro x64. I have all the latest patches for both Office 2010 and Windows 7 installed.

     


    Rupertsland, Canada



    • Edited by rupertsland Wednesday, September 2, 2015 3:16 PM
    Wednesday, September 2, 2015 3:12 PM

Answers

  • SendKeys is notoriously unreliable in recent versions of windows.

    The effect of pressing Ctrl+Home is to select cell A1 in the active sheet. so you could replace

    SendKeys "^{HOME}", True

    with

    Range("A1").Select

    but do you really need to do that? In your example macro, the next line selects another range:

    Range("Project").Select

    so the netto effect of selecting A1 is nil.


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

    • Marked as answer by rupertsland Wednesday, September 2, 2015 4:47 PM
    Wednesday, September 2, 2015 3:46 PM

All replies

  • SendKeys is notoriously unreliable in recent versions of windows.

    The effect of pressing Ctrl+Home is to select cell A1 in the active sheet. so you could replace

    SendKeys "^{HOME}", True

    with

    Range("A1").Select

    but do you really need to do that? In your example macro, the next line selects another range:

    Range("Project").Select

    so the netto effect of selecting A1 is nil.


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

    • Marked as answer by rupertsland Wednesday, September 2, 2015 4:47 PM
    Wednesday, September 2, 2015 3:46 PM
  • Thanks for your explanation and suggestions.

    I wonder why Microsoft even bothers to keep SendKeys, if it doesn't work in Windows 7 anymore (it did before July 2015, and on many computers). They should either fix the problem in Excel, or remove SendKeys from VBA altogether.


    Rupertsland, Canada

    Wednesday, September 2, 2015 4:50 PM