Workbook_open() not working because of timing

[longtran295]

Dear all,
I write some code to expand ribbon when open workbook (in ThisWorkbook):

Private Sub Workbook_open()
If Application.CommandBars("Ribbon").Height<=100 Then
SendKeys "^{F1}", True
End If
End Sub

But it not working. I think because of loading time when file opened.
Could someone has solution for this issue?
Many thanks!

 

[Dan_W]

Following the logic of your code, it will only execute if the height of the Ribbon is equal to or less than 100 pixels. On my system, the ribbon (in its collapsed state) is 143 pixels. That means your code would never actually execute on my system. I suspect that may be the issue with your computer too. Try changing the 100 to 150 pixels and see if that works?

 

[offthelip]

If you do want run a macro shortly after the workbook has opened you can use this technique:
put this macro in the workbook open event:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "delaycall"
End Sub

the in a standard module put this code:

Sub delaycall()
If Application.CommandBars("Ribbon").Height <= 100 Then
SendKeys "^{F1}", True
End If
MsgBox (" open delay run")

End Sub

 

[longtran295]

@Dan_W of course my ribbon height <=100
@offthelip your code is a trick but have no way to make job at almost right time workbook opened?

 

[Dan_W]

The solution posted by @offthelip is a useful one, and common for trying to make sure that code will run upon opening a workbook. What you can do is try adjusting the period of time to see what does/does not work. At present the code waits 15 seconds before running the subroutine - perhaps try experimenting with a shorter time frame (1 second?) to see if it will work almost at exactly the same time as the workbook is opened.