Thread: Command Bars Refresh

Is there a way that a CommandBar can be set onto a timer so that it will automatically delete and rebuild itself every couple of seconds?

The reason I ask this is that I have created an Internet style back button that records each worksheet that is viewed, but as it could be multiple worksheets to view. I have tried using the call of the CommandBar on each bit of code that goes from sheet to sheet, but this doesnt seem to load and remember the last sheet viewed.

This is my CommandBar code

Code:

Sub ComBar()
Dim cmdBar As CommandBar
Dim cmdPopup As CommandBarPopup
Dim cmdButton As CommandBarButton
    DeleteComBar
    Set cmdBar = CommandBars.Add(Name:="eagaheat Tracker", _
    Position:=msoBarFloating, Temporary:=True)
    With cmdBar
        .Top = 150
        .Left = 50
        
    Set cmdPopup = .Controls.Add(Type:=msoControlPopup)
    With cmdPopup
        .Caption = " Last View "
        .BeginGroup = True
        Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = Range("LastSheetViewed").Value    ' " Last View "
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "basNavControls.GoToPreviousSheet"
        End With
    End With
    
    Set cmdPopup = .Controls.Add(Type:=msoControlPopup)
    With cmdPopup
        .Caption = " Forward "
        .BeginGroup = True
        Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = Range("ForwardSheetView").Value    ' " Forward "
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "basNavControls.GoToForwardView"
        End With
    End With

    Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = " Home "
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "GoToHome"
        End With
    
    Set cmdPopup = .Controls.Add(Type:=msoControlPopup)
    With cmdPopup
        .Caption = " Menu goes here "
        .BeginGroup = True
        Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = "Button 4a"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "Macro4a"
        End With
        Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = "Button 4b"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "Macro4b"
        End With
    End With
    
    Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = " Download Tracker "
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "Download"
    End With
                
    Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = "Manual Refresh"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "basNavControls.Refresh"
        End With
    
    Set cmdButton = .Controls.Add(Type:=msoControlButton)
        With cmdButton
            .Caption = "Exit Tracker"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "basNavControls.ExitReport"
        End With
        
    .Width = 1000
    .Visible = True
    End With
End Sub

Sub DeleteComBar()
On Error Resume Next
CommandBars("eagaheat Tracker").Delete
On Error GoTo 0
End Sub

Is there away of setting a timer on the code?

Originally Posted by Andy Pope

But I think your approach is wrong. Instead you should keep a stack of the sheets/workbooks visited by tapping into the Workbook and Sheet Activate events within a class that has a application object with events enabled.

Not sure I understand you. Where can I find some info/examples about doing this?