Workbook Open macro not performing as expected

Hi all,

I am using this code to do a few things when the user opens the workbook:

Private Sub Workbook_Open()

Dim ws As Worksheet: Set ws = ActiveSheet
Dim ht1, ht2 As Long

Application.ScreenUpdating = False

    ht1 = Application.CommandBars("Ribbon").Height
    SendKeys "^{F1}", False
    DoEvents
    ht2 = Application.CommandBars("Ribbon").Height
    If ht2 > ht1 Then SendKeys "^{F1}", False
    
Sheets("FNT_PG").Activate

Application.WindowState = xlMaximized 'maximize Excel
ActiveWindow.WindowState = xlMaximized 'maximize the workbook in Excel
    
If Sheets("Misc").Range("Misc_ActivateCode") = "Yes" Then

    'clears all clutter from the screen
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
    Application.DisplayCommentIndicator = xlNoIndicator

End If

    'This is in the case a old navlog is opened when the navlog is visible and not the front page
    If ws.Range("B2").Value = "Crew" Then GoTo SkipZoom

        [ZoomFntPg].Select
        ActiveWindow.Zoom = True
        Range("B8").Select

SkipZoom:
Application.ScreenUpdating = True

End Sub

Initially I minimise the ribbon and lastly I zoom the page to the the screen size. For some reason both of these events will not run in the order written. If I comment out the hide the ribbon code the screen maximise works but if I don't it doesn't work as I think it should. When the hide ribbon code is active it seems as if the zoom is running first and then the ribbon is being hidden leaving the zoom looking quite small.

Does the sendkey code only trigger when the macro finishes even though it is earlier in the code than the zoom?

If so is there a way around this?

Thanks for your help.
Marcus

Hi there Norie,

Tried it and I get this error:

object variable or with block variable not set

Any ideas?

Cheers,
Marcus

Ok I got this to work:

    If Application.CommandBars("Ribbon").Controls(1).Height > 100 Then
        Application.CommandBars.ExecuteMso ("MinimizeRibbon")
    End If

but it still does not seem to execute the code in order. The zoom still happens prior to the minimisation of the ribbon even though the code is not in that order.

On the 15.4 inch screen I am using the workbook zooms to 87% when the ribbon is visible and 97% when the ribbon is minimised. If I save and close the workbook with the ribbon visible and then open the file the Workbook Open code runs and the workbook is zoomed (shrunk in this instance) to 87%. If I run the zoom code again with the ribbon now minimised to zoom corrects to 97% which is now perfect for my screen size.

Hope that makes sense.

Marcus