Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: Why won't Sendkeys work for me?

  1. #11
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by Roger Govier View Post
    Hi Simon

    It is Control + F1 that is being sent, not just F1.
    That is the toggle for minimising and maximising the ribbon, just the same as if you double click any tab on the ribbon.

    It is the only way of minimising that I know - unless you have something else in mind.
    Does this work for you?
    Code:
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  2. #12
    I asked about events, because indeed firing the code in the beforeprint event is too late I expect.
    On your code:

    Adda DoEvents to make it work:
    Code:
    Sub Max_Ribbon()
        If Application.CommandBars("Ribbon").Height < 80 Then
            SendKeys "^{F1}"
            DoEvents
        End If
    End Sub
    Regards,
    Jan Karel Pieterse
    www.jkp-ads.com

  3. #13
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Jan Karel Pieterse View Post
    I asked about events, because indeed firing the code in the beforeprint event is too late I expect.
    He could always use "Cancel= True" in the beforeprint and use his own routine
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #14
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    I'm at work now so don't have access to xl2010 but maybe this Access 2010 code will suit you better (if it works!)
    Code:
    CommandBars.ExecuteMso "MinimizeRibbon"
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #15
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi Jan Karel

    You are a star!!!
    DoEvents is exactly what was needed to get it to fire.

    Putting that into both the Max_Ribbon and Min_Ribbon macros sorts it out so I can maximise the ribbon (if necessary) before Print Preview, and minimise it again afterwards.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #16
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi Simon

    Thanks for your suggestions.
    I was aware of, and have used the
    Code:
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    in other applications, which works very well if I want the ribbon switched off altogether.

    However, the client doesn't want the Ribbon turned off fully for all users.
    We do want to maximise screen real estate for some fairly large reports, so all row and column headers are turned off, and the "normal" mode of operation if for the ribbon to be minimised.

    As I said before, I need it maximised for the Print Preview which is put on the screen, then minimised afterwards.

    Jan Karel's suggestion of adding DoEvents to the code makes it all work just as I wanted.

    I did also try your suggestion
    Code:
    CommandBars.ExecuteMso "MinimizeRibbon"
    but that doesn't work within the code. It returns a Run time error '5'
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  7. #17
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Roger Govier View Post
    Hi Simon

    I did also try your suggestion
    Code:
    CommandBars.ExecuteMso "MinimizeRibbon"
    but that doesn't work within the code. It returns a Run time error '5'
    It turns out that that line was only added for 2010 glad your sorted
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  8. #18
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,308
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Nice work, Jan Karel! Again, haven't seen that before, but will need to keep in mind for future.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #19
    DoEvents sends the keys regardless whether a dialog is showing or not. This enables you to send keys to Excel in the middle of running code.
    Regards,
    Jan Karel Pieterse
    www.jkp-ads.com

Page 2 of 2 FirstFirst 1 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •