Why won't Sendkeys work for me?

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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?
 

Andy Pope

New member
Joined
Mar 22, 2011
Messages
43
Reaction score
0
Points
0
Location
London, England
Website
www.andypope.info
Excel Version(s)
2016
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

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
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

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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...
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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.
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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:
[COLOR=#660066]Application[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]ExecuteExcel4Macro[/COLOR] [COLOR=black]"SHOW.TOOLBAR(""Ribbon"",False)"[/COLOR]
 

Jan Karel Pieterse

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
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
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
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:
[FONT=Courier New]CommandBars.ExecuteMso "MinimizeRibbon"[/FONT]
:)
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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.
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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'
 

Jan Karel Pieterse

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
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.
 
Top