Opening the tab on which remained.

[rediffusion]

Well... for example, I remained on `Developer` tab. I сlosed the book, started it and with opening `Developer` tab! Is it possible?

 

[Jaafar Tribak]

This worked for me in excel 2016:

The code goes in the ThisWorkbook Module... Upon closing the workbook, the code runs the GetActiveRibbonTab function to store the currently active ribbon tab in a workbook level defined Name and then saves the workbook.

Upon re-opening the workbook, the ActivateRibbonTab function is executed to activate the ribbon tab that was active when last closing the workbook.

As I said, not sure if this will work in other versions of excel but it worth trying.

Option Explicit

Private Sub Workbook_Open()
    Call ActivateRibbonTab(TabName:=[LastActiveRibbonTab])
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Names.Add "LastActiveRibbonTab", GetActiveRibbonTab
    Me.Save
End Sub


Private Function GetActiveRibbonTab() As String

    Const CHILDID_SELF = 0&, NAVDIR_FIRSTCHILD = 7&
    Const NAVDIR_LASTCHILD = 8&, NAVDIR_NEXT = 5&
    Const STATE_SELECTED = &H300002  
    Dim accObj As IAccessible, i As Long, j As Long, lChildCount As Long
     
    
    Set accObj = Application.CommandBars("Ribbon")
    
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    lChildCount = accObj.accChildCount
    Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    
    On Error Resume Next
    For i = 1 To lChildCount
        Set accObj = accObj.accNavigate(NAVDIR_NEXT, CHILDID_SELF)
        If UCase(accObj.accName(CHILDID_SELF)) = UCase("Ribbon Tabs") Then
            Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            lChildCount = accObj.accChildCount
            Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            For j = 1 To lChildCount
                Set accObj = accObj.accNavigate(NAVDIR_NEXT, CHILDID_SELF)
                If accObj.accState(0&) = STATE_SELECTED Then
                    GetActiveRibbonTab = accObj.accName(CHILDID_SELF)
                    Exit Function
                End If
            Next j
        End If
    Next i

End Function


Private Function ActivateRibbonTab(ByVal TabName As String) As Boolean

    Const CHILDID_SELF = 0&, NAVDIR_FIRSTCHILD = 7&
    Const NAVDIR_LASTCHILD = 8&, NAVDIR_NEXT = 5&
    Dim accObj As IAccessible, i As Long, j As Long, lChildCount As Long
    
    
    Set accObj = Application.CommandBars("Ribbon")
    
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_LASTCHILD, CHILDID_SELF)
    Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    lChildCount = accObj.accChildCount
    Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    
    On Error Resume Next
    For i = 1 To lChildCount
        Set accObj = accObj.accNavigate(NAVDIR_NEXT, CHILDID_SELF)
        If UCase(accObj.accName(CHILDID_SELF)) = UCase("Ribbon Tabs") Then
            Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            lChildCount = accObj.accChildCount
            Set accObj = accObj.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            For j = 1 To lChildCount
                Set accObj = accObj.accNavigate(NAVDIR_NEXT, CHILDID_SELF)
                If UCase(accObj.accName(CHILDID_SELF)) = UCase(TabName) Then                
                    accObj.accDoDefaultAction CHILDID_SELF                    
                    ActivateRibbonTab = Not CBool(Err.Number)
                    Exit Function
                End If
            Next j
        End If
    Next i

End Function

 

[rediffusion]

@Steve_

I find link. There are two documents but not for x64 Office. How can I make it work for x64 ?

 

[Steve_]

@Steve_

I find link. There are two documents but not for x64 Office. How can I make it work for x64 ?

I am positive that @Jaafar Tribak can probably help you WAY better than I can...He has already written a solution, and his solution does not require external sources. I would call it a 'preferred' solution due to that

 

[rediffusion]

@Jaafar Tribak

I have «Microsoft Office 365 2019».

Not working it's stack here:

Private Sub Workbook_Open()
Call ActivateRibbonTab(TabName:=[LastActiveRibbonTab])
End Sub

 

[Jaafar Tribak]

@Jaafar Tribak

I have «Microsoft Office 365 2019».

Not working it's stack here:

I am afraid, I dont have Office 365 to adapt the code and test it.

Regards.