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.

 

[VoG]

Try

Application.SendKeys ("^{F1}")

 

[mps]

I still get the same problem if I try your solution VoG!! Any other suggestions???

I'm using excel 2010 (32bit) on windows 7 (64bit) if that is of any help.

 

[VoG]

It works for me with Excel 2010 32 bit running under Windows 7 64 bit.

Try commenting out the test for ribbon height and see if it works.

 

[mps]

Commented out the test for ribbon height as suggested, now excel help doesn't appear but the ribbon doesn't minimise either!!

 

[mps]

Here is the full code that the workbook runs on start up, just in case something else in interferring with the sendkeys command????

Private Sub Auto_Open()
'--------------------------------------------------
'Tasks to run automatically when workbook is opened
'--------------------------------------------------
Dim sfilename As String
Dim temp As Double
Dim ttotalfilename As String
Dim wkbsource As Workbook
Dim salesdatafields As Variant
If CommandBars("Ribbon").Height > 100 Then
    Application.SendKeys ("^{F1}") 'make sure ribbon is minimised
    MsgBox ("minimised") 'temporary notification
End If

'set up comboboxes on sales data settings worksheet
salesdatafields = Array("Empty", "Transaction Number", "Item Code", "Item Description", "Quantity", "Net Price Per Item", "Payment Method 1", "Payment Amount 1", "Payment Method 2", "Payment Amount 2", "Payment Method 3", "Payment Amount 3", "Payment Method 4", "Payment Amount 4", "Gross Price Per Item", "Sale Total", "Time of Sale", "Sale Memo", "Tax Class", "Till Currency", "Date of Sale", "Till Number", "Sale Number")
Sheet20.ComboBox1.List = salesdatafields
Sheet20.ComboBox2.List = salesdatafields
Sheet20.ComboBox3.List = salesdatafields
Sheet20.ComboBox4.List = salesdatafields
Sheet20.ComboBox5.List = salesdatafields
Sheet20.ComboBox6.List = salesdatafields
Sheet20.ComboBox7.List = salesdatafields
Sheet20.ComboBox8.List = salesdatafields
Sheet20.ComboBox9.List = salesdatafields
Sheet20.ComboBox10.List = salesdatafields
Sheet20.ComboBox11.List = salesdatafields
Sheet20.ComboBox12.List = salesdatafields
Sheet20.ComboBox13.List = salesdatafields
Sheet20.ComboBox14.List = salesdatafields
Sheet20.ComboBox15.List = salesdatafields
Sheet20.ComboBox16.List = salesdatafields
Sheet20.ComboBox17.List = salesdatafields
Sheet20.ComboBox18.List = salesdatafields
Sheet20.ComboBox19.List = salesdatafields
Sheet20.ComboBox20.List = salesdatafields
Sheet20.ComboBox21.List = salesdatafields
Sheet20.ComboBox22.List = salesdatafields
Sheet20.ComboBox23.List = salesdatafields
Sheet20.ComboBox24.List = salesdatafields
Sheet20.ComboBox25.List = salesdatafields
Sheet20.ComboBox26.List = salesdatafields

Sheet20.ComboBox1.Value = Sheet20.Cells(5, 8)
Sheet20.ComboBox2.Value = Sheet20.Cells(6, 8)
Sheet20.ComboBox3.Value = Sheet20.Cells(7, 8)
Sheet20.ComboBox4.Value = Sheet20.Cells(8, 8)
Sheet20.ComboBox5.Value = Sheet20.Cells(9, 8)
Sheet20.ComboBox6.Value = Sheet20.Cells(10, 8)
Sheet20.ComboBox7.Value = Sheet20.Cells(11, 8)
Sheet20.ComboBox8.Value = Sheet20.Cells(12, 8)
Sheet20.ComboBox9.Value = Sheet20.Cells(13, 8)
Sheet20.ComboBox10.Value = Sheet20.Cells(14, 8)
Sheet20.ComboBox11.Value = Sheet20.Cells(15, 8)
Sheet20.ComboBox12.Value = Sheet20.Cells(16, 8)
Sheet20.ComboBox13.Value = Sheet20.Cells(17, 8)
Sheet20.ComboBox14.Value = Sheet20.Cells(18, 8)
Sheet20.ComboBox15.Value = Sheet20.Cells(19, 8)
Sheet20.ComboBox16.Value = Sheet20.Cells(20, 8)
Sheet20.ComboBox17.Value = Sheet20.Cells(21, 8)
Sheet20.ComboBox18.Value = Sheet20.Cells(22, 8)
Sheet20.ComboBox19.Value = Sheet20.Cells(23, 8)
Sheet20.ComboBox20.Value = Sheet20.Cells(24, 8)
Sheet20.ComboBox21.Value = Sheet20.Cells(25, 8)
Sheet20.ComboBox22.Value = Sheet20.Cells(26, 8)
Sheet20.ComboBox23.Value = Sheet20.Cells(27, 8)
Sheet20.ComboBox24.Value = Sheet20.Cells(28, 8)
Sheet20.ComboBox25.Value = Sheet20.Cells(29, 8)
Sheet20.ComboBox26.Value = Sheet20.Cells(30, 8)
 
Application.EnableEvents = False 'turn off events so we don't cause an error when we write to the current sale worksheet
'make sure the appropriate worksheets are protected
Sheet1.Protect
Sheet5.Protect
Sheet6.Protect
Sheet7.Protect
Sheet8.Protect
Sheet9.Protect
Sheet10.Protect
Sheet11.Protect
Sheet12.Protect
Sheet13.Protect
Sheet14.Protect
Sheet15.Protect
Sheet16.Protect
Sheet17.Protect
Sheet18.Protect
Sheet19.Protect
Sheet20.Protect
'make sure the 'main options' worksheet is visable, all other worksheets are hidden
Sheet5.Visible = xlSheetVisible
Sheet1.Visible = xlSheetHidden
Sheet2.Visible = xlSheetHidden
Sheet3.Visible = xlSheetHidden
Sheet4.Visible = xlSheetHidden
Sheet6.Visible = xlSheetHidden
Sheet7.Visible = xlSheetHidden
Sheet8.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
Sheet10.Visible = xlSheetHidden
Sheet11.Visible = xlSheetHidden
Sheet12.Visible = xlSheetHidden
Sheet13.Visible = xlSheetHidden
Sheet14.Visible = xlSheetHidden
Sheet15.Visible = xlSheetHidden
Sheet16.Visible = xlSheetHidden
Sheet17.Visible = xlSheetHidden
Sheet18.Visible = xlSheetHidden
Sheet19.Visible = xlSheetHidden
Sheet20.Visible = xlSheetHidden

'make sure option to printout receipts is enabled
Sheet1.CheckBox1 = True
'make sure option to print the £ rate of VAT on the receipt if we are using a different currency is enabled
Sheet18.CheckBox1 = True
'set previous sale change as 0
Sheet1.Unprotect
Sheet1.Cells(5, 10) = 0
'set till total to zero
Sheet1.Cells(3, 10) = 0
'set sale number to 1
Sheet1.Cells(2, 10) = 1
Sheet1.Protect
'make sure excel can display any necessary alerts
Application.DisplayAlerts = True
Application.EnableEvents = True

'make sure userforms are hidden and set up as necessary
UserForm1.savebutton.Enabled = False
UserForm1.Hide
UserForm2.Hide
UserForm3.Hide
UserForm4.Hide
UserForm5.Hide
UserForm6.Hide
UserForm7.Hide
'set the 'main options' worksheet as the first sheet that the user will see
Sheet5.Activate
'show welcome screen
UserForm8.Show
'check the directories which we store data in exist
On Error Resume Next
Err.Clear
If Dir(Sheet11.Cells(18, 4), vbDirectory) = "" Then MkDir (Sheet11.Cells(18, 4)) 'check epos data directory exists
If Dir(Sheet11.Cells(18, 4) + "systemdata\", vbDirectory) = "" Then MkDir (Sheet11.Cells(18, 4) + "systemdata\") 'check system data directory exists
If Err.Number <> 0 Then MsgBox ("WARNING!! The directory currently set as the storage directory for the EPOS data does not exist. Please update the file settings!"): Exit Sub
On Error GoTo 0
'make sure there the till total data file if it exists only relates to todays till totals - if it contains till totals from another date delete it
ttotalfilename = Sheet11.Cells(18, 4) + "systemdata\tilltotaldata.xlsx"
If Dir(ttotalfilename) <> "" Then
    Application.ScreenUpdating = False
    Set wkbsource = Workbooks.Open(ttotalfilename, ReadOnly:=True)
        'cell (1,2) in the till total data file always contains the date the till totals in the file relate to
        If wkbsource.Sheets(1).Cells(1, 2) <> Date Then
        wkbsource.Close
        Kill (ttotalfilename)
        Else
        wkbsource.Close
        End If
    Application.ScreenUpdating = True
    
End If

'clear product list which is held locally in this workbook and then update it
Sheet3.Cells.Clear
Call updateproductdata

End Sub

 

[mps]

Ok I have now partially solved the problem.

The application.sendkeys command minimises the ribbon but I had to put it at the end of the startup code and it mysteriously started working. It seems as if the excel does not like running the minimise code directly on start up.

Private Sub Auto_Open()
'--------------------------------------------------
'Tasks to run automatically when workbook is opened
'--------------------------------------------------

'PUT REST OF START UP CODE HERE
 
Application.SendKeys ("^{F1}") 'make sure ribbon is minimised
 
end sub

I also have to now do the sendkeys code to make sure the ribbon is maximised before workbook closes otherwise the ribbon stays minimised. But now the code I have written does not maximise the ribbon ... aaarrrrrghggghh!!!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'make sure ribbon is maximised
Application.SendKeys ("^{F1}")
'make sure workbook is saved before closing
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Any suggestions?

Also, my code doesn't work if the user has the ribbon minimised in excel at startup. The code which should minimise the ribbon the maximises it. Any suggestions on this issues as well because checking the ribbon height causes the minimise code to fail?

 

[mps]

Further trawling of the internet and I found that the problem with testing the ribbon height hindering the sendkeys was due to the face that I used commandbars on its own and not application.commandbars. The following code works:

If Application.CommandBars("Ribbon").Height > 80 Then
Application.SendKeys ("^{F1}") 'make sure ribbon is minimised
End If

However I still cannot get the ribbon to maximise on exit. Any further suggestions?

I cannot understand why Microsoft made this so complicated!!!

 

[xenou]

IMHO whether the ribbon is minimized or not is a user preference. Therefore there is no reason to dictate this with code. If the user wishes the ribbon minimized, they will simply minimize it and that's how it stays.

ξ

 

[mps]

I actually disagree xenou. There is a good reason for me wanting the ribbon minimised and then maximised when the workbook is closed. 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. I want to make sure that excel is left for them in a state where they aren't pulling their hair out asking where the ribbon has gone. For my worksheet though it is not necessary for them to use the ribbon except in the exceptional circumstances as the worksheet is controlled by buttons, macros and userforms.

I am still stuck on returning the ribbon to maximum when the user closes the workbook. My code does not work and I really would like a solution to this. If any one can help I'd be grateful!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'make sure ribbon is maximised
If Application.CommandBars("Ribbon").Height < 80 Then Application.SendKeys ("^{F1}")

'make sure workbook is saved before closing
ActiveWorkbook.Save
End Sub