Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    To hide the ribbon in a particular workbook, i put the following statement into the workbook open event

    Code:
    Application.Sendkeys "^{F1}"
    The ribbon was indeed gone. But, if I opened the workbook a subsequent time, the ribbon re-appeared.
    The above instruction does act as a toggle switch.

    Hence the question: can I check for the presence of the ribbon, before issuing the instruction?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have a look at this thread from the databases forum.

    I have not tried this in Excel.

    It seems to me that ctrl F1 does not actually hide the ribbon- it just minimises it. Is that what you want?
    Regards
    John



  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This line ought to work.
    It did for me.
    It Hides the ribbon rather than Minimising it

    But be aware it hides it in ALL Open workbooks,
    so if you need to have them appear when the book changes,
    then put the code on the Window activate event and deactivate events.


    Code:
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) &  ",False)"
    To Turn back on Use this

    Code:
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) &  ",True)"
    Code:
    
    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",False)"
    End Sub
    
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",True)"
    End Sub
    Andrew

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Andrew W View Post
    It Hides the ribbon rather than Minimising it
    When I tested this line in Excel it just minimised the ribbon, then as rvWoody said, it returned it to normal again next time.

    This is what I expected as that is exactly what happens in Access. Hence my post about finding the height of the ribbon.

    [attachment=87975:ribbon.gif]
    Attached Images Attached Images
    Regards
    John



  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That's interesting because it hid it on mine.

    [attachment=87977:HiddenRibbon.jpg]
    Attached Images Attached Images
    Andrew

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Your screen shot is what I see for the workbook, but not for Excel. I get this sort of view:
    [attachment=87978:ALT-F1 Excel.jpg]
    However, once the ribbon is minimized it stays that way the next time I open Excel.
    Attached Images Attached Images
    Wendell

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This code is just the equivalent of typing Ctrl+F1.

    According to Help this minimises or restores the ribbon.

    [attachment=87979:ribbon2.gif]
    Attached Images Attached Images
    Regards
    John



  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This code is just the equivalent of typing Ctrl+F1.
    Not on mine.


    CTRL F1 minimises the ribbon on and off

    If I run
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",False)"

    Then the ribbon disappears altogether.
    That is why I put it on the Activate & De-Activate Events to make sure it only applied to a specific book.
    I was not trying to lose it altogether, just when a specific workbook is activated.

    If I remove the De-Activate code then it stays hidden regardless of changing to another workbook.
    If I leave this off then it stays hidden until Excel is closed.
    Next time Excel opens the ribbon returns.





    Andrew

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am talking about this bit of code

    Code:
    Application.Sendkeys "^{F1}"
    as in the first post.

    Your code hides the ribbon. The code posted at the start minimises/restores it.


    What we don't know if which of these rvWoody wants to do.
    Regards
    John



  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To detect whether r not the ribbon is minimized, you can check the height of the Ribbon Commandbar:
    For me, the height is 63 when minimized and 168 when not. I've seen other values though, so to be on the safe side I test for >100:

    If CommandBars("Ribbon").Height > 100 Then
    'Ribbon is NOT minimized
    Else
    'Ribbon is Minimized
    End If
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by pieterse View Post
    To detect whether r not the ribbon is minimized, you can check the height of the Ribbon Commandbar:
    For me, the height is 63 when minimized and 168 when not. I've seen other values though, so to be on the safe side I test for >100:

    If CommandBars("Ribbon").Height > 100 Then
    'Ribbon is NOT minimized
    Else
    'Ribbon is Minimized
    End If
    That is exactly what was in the post I linked to at the beginning of this thread.
    Regards
    John



  12. #12
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Thanks for your replies and sorry for the delay in answering....

    I have to admit that I didn't think of the distinction between hiding and minimizing the ribbon.At a fist glance minimizing would be OK. But I'm confused as to the actual effect sending ctrl+F1

    I did the following test on an empty workbook with the activate/deactivate events coded as follows: (I do want to restore the Ribbon, if I minimized it before)

    Code:
    Private bIShrankTheRibbon As Boolean 
    
    Private Sub Workbook_Activate()
    With Application
        If Val(.Version) >= 12 Then 'Minimize ribbon if present
            If .CommandBars.Item("Ribbon").Height > 100 Then
                .SendKeys "^{F1}"
                bIShrankTheRibbon = True
            End If
        End If
    End With
    End Sub
    
    Private Sub Workbook_Deactivate()
    If bIShrankTheRibbon Then Application.SendKeys "^{F1}"
    End Sub
    
    Private Sub Workbook_Open()
    bIShrankTheRibbon = False
    End Sub
    When the workbook become visible, the Ribbon was minimized. Its actual height was now 59 down from a previous 150. When I opened an Access application, its ribbon too was minimized. Upon closing the workbook (X button upper right corner) the deactivate code with the sendkeys was executed. But when I now opened a fresh excel workbook or an access application, the ribbon was still minimized! As if the Sendkeys in the deactivate event were never executed (or twice executed?) What happened?

    Moving the activate code to the Open event and the Deactivate code to the BeforeClose event gave the same result: the ribbon was minimized and stayed so. As I last resort, I used the WindowActivate and WindowDeactivate event, but still the same result.

    This odd(?) behaviour, together with the rather arbitrary limit(100) to the height of the ribbon made me switch to the "Cambridge" solution:

    Code:
    Private bIShrankTheRibbon As Boolean
    
    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    bIShrankTheRibbon = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",False)"
    bIShrankTheRibbon = True
    End Sub
    
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If bIShrankTheRibbon Then _
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",True)"
    End Sub
    I'll stick with that solution for the moment, but I'm not reassured that it will be a lasting solution, as I fear that someday Excel4Macro support could be dropped. The following excerpt seems to point in that direction:

    Migrating Excel 4 Macros to VBA

    Prior to the introduction of VBA into Excel, we had our own macro facility – known as Excel 4 macros, used in Excel macro sheets. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

    In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. This work generally fell into two categories:

    Adding new objects, methods and properties to Excel’s object model for the few remaining Excel 4 macro functions that did not have equivalents in VBA.
    Performance Improvements for Page Setup operations in VBA to provide similar performance to PAGE.SETUP() functionality in Excel 4 Macros.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    That is exactly what was in the post I linked to at the beginning of this thread.
    Duh, I overlooked that part. Funny that I arrived at the exact same solution.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This seems to work fine:
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    JPK: the DoEvents seem to be essential for success! The following code worked as you suggested:

    Code:
    Private bIShrankTheRibbon As Boolean
    
    Private Sub Workbook_Activate()
    With Application
        If Val(.Version) >= 12 Then 'Minimize ribbon if present
            If .CommandBars.Item("Ribbon").Height > 100 Then
                .SendKeys "^{F1}"
                bIShrankTheRibbon = True
                DoEvents
            End If
        End If
    End With
    End Sub
    
    Private Sub Workbook_Deactivate()
    If bIShrankTheRibbon Then
        Application.SendKeys "^{F1}"
        DoEvents
    End If
    End Sub
    
    Private Sub Workbook_Open()
    bIShrankTheRibbon = False
    End Sub
    Without that statement, the ^{F1} was apparently sent to whatever was active at that moment. At some point I even lost my numeric pad on the keyboard, or the Help menu popped up when it was not expected. If however you open a second workbook, while the first is still open, that too will have its ribbon hidden.

    Therefore, I remain with the Excel4Macro (but I keep this solution in my archive, in case these macros loose their attractiveness)
    Life is full of compromises!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •