Why won't Sendkeys work for me?

[Roger Govier]

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.

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?

[Andy Pope]

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.

[Roger Govier]

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.

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

[Jan Karel Pieterse]

When is max_ribbon called exaclty, by the before_print event?

[Ken Puls]

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?

[Jan Karel Pieterse]

I've seen it many times before: code that works from a forms button or a menu item, but not from the macro dialog.

[Roger Govier]

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!!!

[Ken Puls]

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...

[Simon Lloyd]

Hi Roger, why are you using F1?

[Roger Govier]

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.