Expand/collapse toolbar ribbon

[jday]

I am having an issue with my Auto_Open and Auto_Close macros, where I am
basically trying to make sure the toolbar ribbon is "collapsed" when the user
opens the workbook, and "expanded" when they close it. I found a suggestion
from a previous post on this topic -- below is the code that was recommended:

(Auto_Open)
If Application.CommandBars("Ribbon").Height > 80 Then SendKeys "^{F1}"

(Auto_Close)
If Application.CommandBars("Ribbon").Height < 80 Then SendKeys "^{F1}"

The Auto_Open macro works correctly---it ensures the ribbon is "collapsed"
upon opening the file. The problem is when the file is closed---for some
reason, instead of expanding the ribbon, it opens up the Help window instead
(the ribbon stays collapsed). Ideally, I would like to have the Auto_Close
procedure "restore" the ribbon to whatever the user had previously (either
collapsed or expanded) -- but I would settle for having it "always" expand
the ribbon when the file is closed to accommodate majority of users. What is
wrong with my current Auto_Close procedure?

 

[jday]

This is SO, SO close! The only thing is when the file is opened, I still
want the menu 'tabs' to be visible so the user can select them if necessary.
In other words, I only want the menu ribbon itself to be collapsed---not the
tabs or the user's quick access toolbar. Is there anything else I can try??
If not, I will definitely go forward with your suggestion since it is still
better than what I had before!

 

[Rick Rothstein]

Ah yes, I see that Ctrl+F1 does leave the menu tabs... sorry, but I'm not
sure how to do that in code. I'll experiment a little bit to see if I can
uncover something, so check back into this thread later to see if I found
anything or not.

 

[jday]

Definitely will do---thanks!

Ah yes, I see that Ctrl+F1 does leave the menu tabs... sorry, but I'm not
sure how to do that in code. I'll experiment a little bit to see if I can
uncover something, so check back into this thread later to see if I found
anything or not.

 

[Ron de Bruin]

Hi jday

Use RibbonX to do this if you use a 2007 file format
There are workbook and xml examples on my site (point 2)
http://www.rondebruin.nl/ribbon.htm

 

[Rick Rothstein]

Okay, this should work. It is a toggle macro, so call it to hide the Ribbon
if it is showing or show the Ribbon if it is hidden. To use it, copy/paste
it into a Module (Insert/Module from the VB editor's menu bar), then just
run the SendCtrlF1 macro...

'*************** START OF CODE ***************
Private Type GENERALINPUT
dwType As Long
xi(0 To 23) As Byte
End Type

Private Type KEYBDINPUT
wVk As Integer
wScan As Integer
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type

Private Const INPUT_MOUSE = 0
Private Const INPUT_KEYBOARD = 1
Private Const INPUT_HARDWARE = 2
Private Const VK_F1 = &H70
Private Const VK_CONTROL = &H11
Private Const KEYEVENTF_KEYUP = &H2
Private Const KEYEVENTF_EXTENDEDKEY = &H1

Private Declare Function SendInput _
Lib "user32.dll" _
(ByVal nInputs As Long, _
PINPUTS As GENERALINPUT, _
ByVal cbSize As Long) As Long

Private Declare Sub CopyMemory _
Lib "kernel32.dll" _
Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)

Private Declare Function MapVirtualKey _
Lib "user32" _
Alias "MapVirtualKeyA" _
(ByVal wCode As Long, _
ByVal wMapType As Long) As Long

Sub SendCtrlF1()
Dim GInput(3) As GENERALINPUT
Dim KInput As KEYBDINPUT
' Press the Ctrl Key
KInput.wVk = VK_CONTROL
KInput.dwFlags = 0
GInput(0).dwType = INPUT_KEYBOARD
CopyMemory GInput(0).xi(0), KInput, Len(KInput)
' Press the F1 Key
KInput.wVk = VK_F1
KInput.dwFlags = 0
GInput(1).dwType = INPUT_KEYBOARD
CopyMemory GInput(1).xi(0), KInput, Len(KInput)
' Release the F1 Key
KInput.wVk = VK_F1
KInput.wVk = VK_F1
KInput.dwFlags = KEYEVENTF_KEYUP
KInput.dwFlags = KEYEVENTF_KEYUP
GInput(2).dwType = INPUT_KEYBOARD
GInput(2).dwType = INPUT_KEYBOARD
CopyMemory GInput(2).xi(0), KInput, Len(KInput)
CopyMemory GInput(2).xi(0), KInput, Len(KInput)
' Release the Ctrl Key
KInput.wVk = VK_CONTROL
KInput.dwFlags = KEYEVENTF_KEYUP
GInput(3).dwType = INPUT_KEYBOARD
CopyMemory GInput(3).xi(0), KInput, Len(KInput)
' Send the keystrokes
SendInput 4, GInput(0), Len(GInput(0))
End Sub
'*************** END OF CODE ***************

 

[Rick Rothstein]

It occurs to me that, for the purpose you originally mentioned, a toggle
macro would not be useful (because you don't necessarily know the current
state the user has the Ribbon in). Add these two macros to the Module along
with all the other code I posted and call them as needed (instead of calling
the toggle macro directly)...

Sub ShowRibbon()
If Application.CommandBars("Ribbon").Height < 100 Then SendCtrlF1
End Sub

Sub HideRibbon()
If Application.CommandBars("Ribbon").Height > 100 Then SendCtrlF1
End Sub

Note... the value of 100 that I am testing against was empirically
derived... on my system, the height of the Ribbon when displayed is 146 and
its height when hidden is 55 (the height of the menu I presume), so I chose
100 as the "middle ground" figuring it should work on any system. If anyone
knows more about how the height can vary across different display settings,
please post that information here.