Can't Return Both Ribbon Heights

[WilliamJones]

Given users will have different monitor sizes and different screen resolutions, I'm trying to write a routine that will allow me to see if the ribbon is maximized or minimized based on its height.Unfortunately, through experimentation I've found out that Excel doesn't like running send key commands. For example I discovered a bug where if you run the send key for minimizing the ribbon twice, each followed by a msgbox, excel acts as if Help (F1) was called instead of control f1...actually it runs both sendkeys and both Help calls...removing the msgbox clears the errors..go figure.


Here's what I'm trying to do. I got this to work by using a wait command but that screwed up the way the screen repainted.

Please Login or Register  to view this content.

I've tried all kinds of workarounds but nothing works.

[Leith Ross]

Hello WilliamJones,

If you want to know the state of the Excel window when your code is running, you can use Application.WindowState. You can check the state using the following constants: xlMaximized, xlMinimized, or xlNormal.

[WilliamJones]

Hello WilliamJones,

If you want to know the state of the Excel window when your code is running, you can use Application.WindowState. You can check the state using the following constants: xlMaximized, xlMinimized, or xlNormal.

Yes I know. I'm trying to see if the user has the ribbon minimized or not, not the window state.

[romperstomper]

As a matter of interest, why?

[WilliamJones]

As a matter of interest, why?

Hey
I'm creating a dashboard with custom ribbon than can be used on laptops (with different resolutions) as well as desktop monitors. I want to offer two views: full real estate (with ribbon minimized) and normal real estate (with ribbon maxed).
Full Screen isn't an option because I still want the user to access the custom ribbon - for example I have checkboxes on the ribbon that add or delete products (i.e chart series) based on the users selection(s).

The flow would be like this. User selects full view (or whatever I'll call it) from the custom ribbon. The vba code checks to see the state. If the ribbon is maximized the chart embedded on the worksheet is sized accordingly through vba, or conversely chart is downsized.
The problem is checking the state. MS didn't plan for this so I was hoping the workaround would be to use the height property. Unfortunately the height changes from PC to PC, so instead I used a different approach -the code I posted above should work fine...but it doesn't.

Interestingly, the code above will work if you put in a wait condition. But then it completely screws up the screen...the charts aren't repainted, or are partially redrawn.
Also amusing, if you try to put a msgbox to debug the code, Excel goes berserk...brings up the help dialog .....totally retarded.

Make sense?

[romperstomper]

Sort of makes sense, except why you need to know the ribbon state in order to size a chart?

[shg]

Why not use ActiveWindow.VisibleRange, and then size the chart within those extents?

[WilliamJones]

Sort of makes sense, except why you need to know the ribbon state in order to size a chart?

The ribbon state maximized allows for larger chart. Sizing is done automatically without specific user input.

As user, you hit the toggle button for the view.
If ribbon state is minimized, maximize ribbon, minimize chart size
If ribbon state is maximized, minimize ribbon, maximize chart size.

I should add the sheet may contain tables also or a combination of charts and tables. In other words, there are times where it will be beneficial to minimize the ribbon to allow for more rows of data to be visible.

In the final interface, I plan to remove all of excels native tabs and I don't expect the user to use the context menu to minimize/restore ribbon.

[shg]

I don't like code that thinks it has better ideas about my environment than I do.

[WilliamJones]

I don't like code that thinks it has better ideas about my environment than I do.

you must love Microsoft software in general then.
on a more serious note have you ever worked with typical users?
you'd be very surprised at their lack of knowledge of things power users consider to be very basic.

thanks for you post....very helpful.

[shg]

you must love Microsoft software in general then.

Despite all the flak people are wont to dish at Microsoft, I think the overwhelming dominance of the markets they serve is ample witness to the quality of the Office suite. If your code improves the user's experience, it would be among the hen's teeth of exceptions in mine.

That's just an observation.

[WilliamJones]

If your code improves the user's experience, it would be among the hen's teeth of exceptions in mine.

I'm not sure if you understand what I'm trying to do.

[WilliamJones]

With the help from an expert on another forum, I was able to find an excellent solution using doEvents.

So what will this code be used for?

You have a workbook with an application specific custom ribbon that you want visible at all times (i.e fullscreen is not an a option) but you want at times to increase the screen height to maximize your view. The sheet is protected and there are embedded charts on the sheet as well as other objects.

In just one click, a toggle, VBA determines if your ribbon is already max'd or min'd, it unprotects the sheet, resizes the graph and other objects, re-protects the sheet...all done programatically and all done while keeping the custom ribbon visible..allowing more changes. Click again and you revert back to the previous state. this enhances the user experience dramatically.

Please Login or Register  to view this content.

[shg]

Thanks for posting back with your solution.