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.
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.
I have tried it with adding a Wait to the Sendkeys event, but that makes no difference.Code:Sub Max_Ribbon() If Application.CommandBars("Ribbon").Height < 80 Then With ThisWorkbook.ActiveSheet SendKeys "^{F1}" End With End If End Sub
What am I doing wrong?
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.
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
When is max_ribbon called exaclty, by the before_print event?
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
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.
I've seen it many times before: code that works from a forms button or a menu item, but not from the macro dialog.
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!!!
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
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.
Hi Roger, why are you using F1?
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.
Bookmarks