Toolbars - re-instating automatically after dis-appearing

[cresbydotcom]

After loosing toolbars regularly due to re-boots after lock-out when powered down (it is an ATI TV app bug!) this routine was devised to re-instate a toolbar with consistent parameters. The routines reside in fred.xls & not in the Active.Workbook. The real toolbar is much more comprehensive.

Private Sub retool_Click()
'This macro generates a tool bar called 'local' and gives it specific properties
    Application.CommandBars.Add(Name:="local").Visible = True
    Application.CommandBars("local").Controls.Add Type:=msoControlButton, _ 
      ID:= 2950, Before:=1
      With Application.CommandBars("local").Controls.Item(1)
       .Caption = "TV"
       .Style = msoButtonCaption
       .OnAction = "'C:\My Documents\fred.xls'!runTV"
      End With
    Application.CommandBars("local").Controls.Add Type:=msoControlButton_ 
      ID:= 2950, Before:=2
      With Application.CommandBars("local").Controls.Item(2)
       .Caption = "Dance"
       .Style = msoButtonCaption
       .OnAction = "'C:\My Documents\fred.xls'!rundance"
      End With
End Sub

relevant FAQ

 

[SkipVought]

cresbydotcom,

Code readablilty helps in so many ways. See if this helps...

Private Sub retool_Click()
'This macro generates a tool bar called 'local' and gives it specific properties
    Dim cbr As CommandBar, cbt As CommandBarButton, xl As Application
    
    Set xl = Application
    
    Set cbr = xl.CommandBars.Add
    
    With cbr
        .Name = "local"
        .Visible = True

        Set cbt = .Controls.Add( _
            Type:=msoControlButton, _
            ID:=2950, _
            Before:=1)
        With cbt
            .Caption = "TV"
            .Style = msoButtonCaption
            .OnAction = "'C:\My Documents\fred.xls'!runTV"
        End With

        Set cbt = .Controls.Add( _
            Type:=msoControlButton, _
            ID:=2950, _
            Before:=1)
        With cbt
            .Caption = "Dance"
            .Style = msoButtonCaption
            .OnAction = "'C:\My Documents\fred.xls'!rundance"
        End With
    End With
    
    Set cbt = Nothing
    Set cbr = Nothing
    Set xl = Nothing
    
End Sub

Skip,

_[red][/red]

 

[cresbydotcom]

ta - cut and paste based on exact code is less error prone - hence format.

The methodology was to use watch terms that are near enough to create a list of properties. The expanded list can be eye-balled for suitable properties that will give required results. The content of eache properties is further clue as to their function. It is lazy but easier.

 

[SkipVought]

"...cut and paste based on exact code is less error prone ..."

Did you ever see a recorded macro? ugh!

But, as one surgeon said to another...

suture self!

Skip,

_[red][/red]

 

[cresbydotcom]

Yup I am in stitches, or are you needling me?

record macros all the time - lazy again but effective -until Micro$oft got it wrong - then "you are on yer own PAL