Forum Discussion
minimized ribbon not consistent
Mr Mickle,
Thank you for taking time out to answer my query.
below is what I want for my worksheet page to look like:
there is still some lines above showing.. this is what my code does, only that it sometimes doesn't!
the code you provided is a little "too minimized", as below:
maybe a little more tweaking....
many many thanks
Try this snippet instead. I've added in a DoEvents statement:
Private Sub Workbook_Open()
If Application.CommandBars("Ribbon").Height >= 100 Then
SendKeys "^{F1}", False
DoEvents
End If
Application.CommandBars("ply").Enabled = False
Application.DisplayFormulaBar = False
End Sub
- Mr Mickle
below is the full script of the workbook_open event,
maybe there is something wrong with it....
many thanks
Private Sub Workbook_Open()
If Application.CommandBars("Ribbon").Height >= 100 Then
SendKeys "^{F1}"
End If
Application.CommandBars("ply").Enabled = False
Application.DisplayFormulaBar = False
Application.OnKey "{F1}", ""
Application.OnKey "{F3}", ""
Application.OnKey "{F4}", ""
Application.OnKey "{F5}", ""
Application.OnKey "{F6}", ""
Application.OnKey "{F7}", ""
Application.OnKey "{F8}", ""
Application.OnKey "{F10}", ""
Application.OnKey "{F11}", ""
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A" & LastRow + 1).Select
End Sub- I inserted the script DoEvents - but to no avail.....
First try this code by itself:
Private Sub Workbook_Activate() Dim LastRow As Long If Application.CommandBars("Ribbon").Height >= 100 Then SendKeys "^{F1}", False DoEvents blnOpen = True Application.CommandBars("ply").Enabled = False Application.DisplayFormulaBar = False Application.OnKey "{F1}", "" Application.OnKey "{F3}", "" Application.OnKey "{F4}", "" Application.OnKey "{F5}", "" Application.OnKey "{F6}", "" Application.OnKey "{F7}", "" Application.OnKey "{F8}", "" Application.OnKey "{F10}", "" Application.OnKey "{F11}", "" With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("A" & LastRow + 1).Select End With End If End SubIf it doesn't work then try this instead:
Try putting this code in the ThisWorkbook Module:
Private Sub Workbook_Activate() Dim LastRow As Long If blnOpen = False Then If Application.CommandBars("Ribbon").Height >= 100 Then SendKeys "^{F1}", False DoEvents blnOpen = True Application.CommandBars("ply").Enabled = False Application.DisplayFormulaBar = False Application.OnKey "{F1}", "" Application.OnKey "{F3}", "" Application.OnKey "{F4}", "" Application.OnKey "{F5}", "" Application.OnKey "{F6}", "" Application.OnKey "{F7}", "" Application.OnKey "{F8}", "" Application.OnKey "{F10}", "" Application.OnKey "{F11}", "" With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("A" & LastRow + 1).Select End With End If End If End SubAnd this code in a Normal Code Module:
Public blnOpen As Boolean
I can't reproduce the issue...what scenario does it not work in? Is there other code running...
Best I can guess is when you disable the F1 key with
Application.OnKey "{F1}", ""it messes up the macro
1. Do you have other workbooks open when it doesn't work?
2. Does the code work when you only have one workbook open?
You may want to take a look at this documentation:
https://www.rondebruin.nl/win/s4/win012.htm
You may try assigning a macro to the OnKey "^{F1}"
Mr. Mickle
Thank you for your prompt reply.
But, the codes is still not functioning as desired (ribbon still showing).
When the "^" sign at the far right was manually clicked, the ribbon minimizes and next time it opens, it is ok. But shouldn't it worked automatically everytime?
could the value of 100 not correct or something....
many many thanks