Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Why won't Sendkeys work for me?

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

    Why won't Sendkeys work for me?



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

    Hi

    Using XL2007, I have an application where most of the time the users will have the Ribbon minimised.
    However, when I want them to be in a Print Preview mode, following a button click, with a report that I have formatted, I need the Ribbon to be maximised, otherwise you don't see all the Preview options.

    I can identify that the ribbon is minimised easily enough with a test of Commandbars("Ribbon").Height, but when then trying to do sendkeys, there is either no effect whatsoever, or I get the VBE Help (as though the Control hadn't been sent in from of the F1.

    This has been driving me nuts all morning.

    Code:
    Sub Max_Ribbon()
        If Application.CommandBars("Ribbon").Height < 80 Then
            With ThisWorkbook.ActiveSheet
                SendKeys "^{F1}"
            End With
        End If
    End Sub
    I have tried it with adding a Wait to the Sendkeys event, but that makes no difference.

    What am I doing wrong?
    Regards

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

  2. #2
    Acolyte Andy Pope's Avatar
    Join Date
    Mar 2011
    Location
    London, England
    Posts
    37
    Articles
    0
    Hi Roger,

    If a add a forms button to the worksheet and assign your macro it works as expected.
    Minimize ribbon is expanded.

    Can you provide more information on how the ribbon was minimized and how you are triggering the macro.
    Cheers
    Andy

    www.andypope.info

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

    Yep, if I add a forms button and assign the macro - it works!!

    However, within my code it doesn't (code shown below)

    Basically I ask the user a series of questions, and I show the code from the last one onward.
    If the answer is Yes,
    then another gets activated, and summary data copied across before printing.
    If No, then various rows within the existing sheet are unhidden, giving rise to a report which is 4 pages long.

    Code:
    Select Case MsgBox("Do you want a Summary report" _
                         & vbCrLf & "or a Detailed report?" _
                         & vbCrLf & "" _
                         & vbCrLf & "Click YES for Summary  (single page)" _
                         & vbCrLf & "Click NO   for Detailed    ( 4 pages)" _
             , vbYesNoCancel Or vbQuestion Or vbDefaultButton1, Application.Name)
    
        Case vbYes
            Call CopyMatrixSum
        
        Case vbNo
             UnhideTiersM
        
        Case vbCancel
            Exit Sub
        End Select
          
        Max_Ribbon
        
        With ActiveSheet.PageSetup
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = BW
            .Zoom = 59
            If ActiveSheet.Name = "Matrixsum" Then
                .Zoom = 65
            End If
            .PrintErrors = xlPrintErrorsDisplayed
            .AlignMarginsHeaderFooter = True
        End With
     
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
    Regards

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

  4. #4
    When is max_ribbon called exaclty, by the before_print event?
    Regards,
    Jan Karel Pieterse
    www.jkp-ads.com

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Stating the obvious, I know, but it's really weird that this code will work when called from a forms button, but not by just running it. It's almost like it doesn't have a proper focus set or something?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  6. #6
    I've seen it many times before: code that works from a forms button or a menu item, but not from the macro dialog.
    Regards,
    Jan Karel Pieterse
    www.jkp-ads.com

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

    As you can see, it is being called by a line in the macro itself, not by any event code.
    I will try placing it in an event code, Before Print, and see if that has any effect.

    As I said before, its driving me nuts!!!
    Regards

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

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Sorry guys, I just noticed that one of Roger's posts got caught in moderation for some reason... not sure why. So I'm not sure if everyone was seeing post #3 in this thread. I'm going to have to keep a closer lookout for that...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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. #9
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Hi Roger, why are you using F1?

  10. #10
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.

    @Jan Karel
    I had not thought of the Before Print event, but that would be too late anyway, as I want the ribbon maximised as the user gets to Print Preview, so they have all the normal options of print preview available to them, and not just the QAT with a print icon.
    Regards

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

Page 1 of 2 1 2 LastLast

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
  •