| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand/collapse toolbar ribbon
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
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" wrote: Try these instead... Hide: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Show: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" -- Rick (MVP - Excel) "jday" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
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. -- Rick (MVP - Excel) "jday" wrote in message ... 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" wrote: Try these instead... Hide: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Show: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" -- Rick (MVP - Excel) "jday" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
Definitely will do---thanks!
"Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "jday" wrote in message ... 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" wrote: Try these instead... Hide: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Show: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" -- Rick (MVP - Excel) "jday" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "jday" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
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 (MVP - Excel) "jday" wrote in message ... Definitely will do---thanks! "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "jday" wrote in message ... 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" wrote: Try these instead... Hide: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Show: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" -- Rick (MVP - Excel) "jday" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expand/collapse toolbar ribbon
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "jday" wrote in message ... Definitely will do---thanks! "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "jday" wrote in message ... 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" wrote: Try these instead... Hide: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Show: ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" -- Rick (MVP - Excel) "jday" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i expand and collapse rows | New Users to Excel | |||
| Expand Collapse Buttons | Excel Discussion (Misc queries) | |||
| Expand/collapse toolbar ribbon | Excel Programming | |||
| Expand/collapse | Excel Programming | |||
| expand/collapse row button | Excel Discussion (Misc queries) | |||
Linear Mode
