How to create navigation with VBA

Can you upload sample of your workbook with the expected output?

It sounds like you are trying to create a situation where clicking on different parts of a string will bring you to different places in a workbook.

Where is the string that you would click on?

In your example "start / active worksheet name", what is "start"? a workbook?

So the string is not a structure of different levels (like the navigation on this website) but a history of your "travels".

Where do you want this string?

For Excel, there are three levels, Workbook, Worksheet and Range

A level based string would look like Book1 > Home > $A$1
And when the user went to the report sheet the string would look like Book1 > Report > $A$1

In post #9, the string Home > Report > May is a history of what sheets you traveled to (all on the same level)

The attached sheet has a user-defined heirachy

Home
-Report
--Jan
--Feb
--Mar
-Sales
--January
--February
---ValentinesSales
--March

The sub SetHeirarchy allows you to specify which sheet is the immediate superior. It should be run everytime you make a new worksheet. Note that the top level sheet (Home) has no superior.

Navigating between the sheets will create a (temporary) floating command bar that you can click on to navigate to the indicated sheet.

' in ThisWorkbook code module

Private Sub Workbook_Activate()
    Call Workbook_SheetActivate(ActiveSheet)
End Sub

Private Sub Workbook_Deactivate()
    CustomCommandBar.Visible = False
End Sub

Public Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim xString As String
    Dim aSheet As Worksheet
    Set aSheet = Sh
    Application.ScreenUpdating = False
    With CustomCommandBar
        Do Until .Controls.Count = 0
            .Controls(1).Delete
        Loop
    
        Do
            MakeButton(aSheet.Name, "GoToSelectedSheet").Tag = aSheet.Name
            Set aSheet = ParentSheet(aSheet)
        Loop Until (aSheet Is Nothing)
    
    End With
    Application.ScreenUpdating = True
End Sub

' in a normal module

Sub SetHeirchy()
    Dim oneSheet As Worksheet
    Dim strPrompt As String, strDefault As String
    Dim uiParentSheet As String, uiCodeName As String
    For Each oneSheet In ThisWorkbook.Sheets
    
        Do
            strPrompt = "What sheet is immediatly above " & oneSheet.Name & "?"
            strDefault = vbNullString
            On Error Resume Next
            strDefault = ParentSheet(oneSheet).Name
            On Error GoTo 0
            uiParentSheet = Application.InputBox(strPrompt, Default:=strDefault, Type:=2)
            If uiParentSheet = "False" Then Exit Sub: Rem canceled
            
            If uiParentSheet = vbNullString Then
                uiCodeName = vbNullString
            Else
                uiCodeName = Chr(5)
                On Error Resume Next
                uiCodeName = ThisWorkbook.Sheets(uiParentSheet).CodeName
                On Error GoTo 0
            End If
            If uiCodeName = Chr(5) Then
                MsgBox "Bad tab name. Try again"
            Else
                oneSheet.Names.Add(Name:="_ParentSheetCodeName", RefersTo:="=""" & uiCodeName & """").Visible = False
            End If
        Loop Until uiCodeName <> Chr(5)
    Next oneSheet
End Sub

Function ParentSheet(ChildSheet As Worksheet) As Worksheet
    Dim ParentCodeName As String
    Dim oneSheet As Worksheet
    On Error Resume Next
    ParentCodeName = Evaluate(ChildSheet.Names("_ParentSheetCodeName").RefersTo)
    On Error GoTo 0
    If ParentCodeName = vbNullString Then Exit Function
    For Each oneSheet In ThisWorkbook.Worksheets
        If oneSheet.CodeName = ParentCodeName Then
            Set ParentSheet = oneSheet
            Exit For
        End If
    Next oneSheet
End Function

Function MakeButton(NewCaption As String, Optional NewAction As String = vbNullString) As CommandBarButton
    With CustomCommandBar
        Set MakeButton = .Controls.Add(Type:=msoControlButton, before:=1, temporary:=True)
        With MakeButton
            .BeginGroup = True
            .Style = msoButtonCaption
            .Caption = NewCaption
            .Visible = True
            .OnAction = NewAction
        End With
        .Visible = True
    End With
End Function

Function CustomCommandBar() As CommandBar
    On Error GoTo MakeBar
    Set CustomCommandBar = Application.CommandBars("Nav")
    On Error GoTo 0
Exit Function
MakeBar:
    Err.Clear
    Set CustomCommandBar = Application.CommandBars.Add("Nav", Position:=msoBarFloating, temporary:=True)
End Function

Function GotoSelectedSheet()
    With CustomCommandBar
        Sheets(.Controls(1 + (Application.Caller(1) \ 2)).Tag).Activate
        Call ThisWorkbook.Workbook_SheetActivate(ActiveSheet)
    End With
End Function

I hope this helps.

Hi, thank you for your reply! I have tried your attached workbook and copy & paste to a new document and I don't see any floating command bars :/

SetHeirarchy works and I have tried running it from a shape also. Where is the floating bar supposed to be?

If you open my workbook and switch between sheets, does the command bar appear?

Try changing this part of the code (in Module 1).
Then save the file, QUIT excel (don't just close the workbook) and the open the workbook again.

Function CustomCommandBar() As CommandBar
    On Error GoTo MakeBar
    Set CustomCommandBar = Application.CommandBars("Nav")
    On Error GoTo 0
Exit Function
MakeBar:
    Err.Clear
    Set CustomCommandBar = Application.CommandBars.Add("Nav", Position:=msoBarFloating, temporary:=True)
    CustomCommandBar.Top = 400
    CustomCommandBar.Left = 600
End Function

Yes, it works for me (Excel 2011)

Getting the string is one thing, but it has to be put in a place where clicking on it (or a portion of it) will take you to the desired sheet.