Sendkeys and minimising ribbon problem

[mps]

Hi I am trying to minimise the ribbon in workbook when it loads using the following code:

Private Sub Auto_Open()
If CommandBars("Ribbon").Height > 100 Then
    Application.SendKeys "^{F1}", False  'make sure ribbon is minimised
    MsgBox ("minimised")    
End If
End Sub

Frustratingly it checks the the ribbon height ok, but all sendkeys ends up doing is showing Excel help, rather than minising the ribbon. For some reason it is not picking up ^ as 'ctrl'.

Can anyone help? I do only want the ribbon minimised and not hidden, so the the user can still access excel's menus.

 

[mps]

Finally after a lot of fiddling around and trying different combinations my maximise ribbon on close code works. The problem? Changing 'application.sendkeys' to just 'sendkeys' in the beforeclose event made it work! Where as I needed the word application before commandbars on loading the workbook to make the ribbon minimise, on closing the the workbook I needed to take out the word application from in front of sendkeys .... how crazy is that!! Either something is going on I don't understand or VBA is just a bit crazy here!!

The working code is as follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.CommandBars("Ribbon").Height < 80 Then
    SendKeys ("^{F1}")
    DoEvents
End If
'make sure workbook is saved before closing
ActiveWorkbook.Save
End Sub

Hope this helps someone else to avoid having a headache trying work this out!

 

[xenou]

The users who will be using my worksheet are not excel 'power' users and therefore do not know how to minimise / maximise the ribbon manually

This is not something that falls in the domain of advanced excel knowledge. You would do better to show your user how to minimize the ribbon *if that is their preference*.

ξ