I'm using Exel 2003 and not sure if I can do this but it will work when stepping through the code and won't work when I open a new instance of Excel. This is my first time working with Excel Addins so I'm not sure the best way to do it. Just trying to make sure a commandbar exists if it doesn't create it then add a commandbarbutton. Any help would be great!
Dim cbar As CommandBarButton
Dim tbar As CommandBar
Private Sub Workbook_AddinInstall()
On Error Resume Next
If Not CbarExists Then
Set tbar = Application.CommandBars.Add
With tbar
.Name = "COE"
.Position = msoBarTop
.Visible = True
End With
End If
'Remove the Command Bar Button if it exists
Application.CommandBars("COE").Controls("S2_Data").Delete
'Add the button back into the CommandBar
Set cbar = Application.CommandBars("COE").Controls.Add(msoControlButton)
With cbar
.Style = msoButtonIconAndWrapCaption
.FaceId = 3051
.Caption = "S2_Data"
.OnAction = "Get_S2Data"
.BeginGroup = True
End With
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("COE").Controls("S2_Data").Delete
On Error GoTo 0
End Sub
Function CbarExists() As Boolean
Dim cbar As CommandBar
For Each cbar In Application.CommandBars
If cbar.Name = "COE" Then
CbarExists = True
Exit Function
End If
Next cbar
CbarExists = False
End Function
Dim cbar As CommandBarButton
Dim tbar As CommandBar
Private Sub Workbook_AddinInstall()
On Error Resume Next
If Not CbarExists Then
Set tbar = Application.CommandBars.Add
With tbar
.Name = "COE"
.Position = msoBarTop
.Visible = True
End With
End If
'Remove the Command Bar Button if it exists
Application.CommandBars("COE").Controls("S2_Data").Delete
'Add the button back into the CommandBar
Set cbar = Application.CommandBars("COE").Controls.Add(msoControlButton)
With cbar
.Style = msoButtonIconAndWrapCaption
.FaceId = 3051
.Caption = "S2_Data"
.OnAction = "Get_S2Data"
.BeginGroup = True
End With
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("COE").Controls("S2_Data").Delete
On Error GoTo 0
End Sub
Function CbarExists() As Boolean
Dim cbar As CommandBar
For Each cbar In Application.CommandBars
If cbar.Name = "COE" Then
CbarExists = True
Exit Function
End If
Next cbar
CbarExists = False
End Function