how to maintain add-ins? (with toolbars)

  • Thread starter Thread starter akh2103
  • Start date Start date
A

akh2103

Hello-- I have written a series of programs linked to buttons on a
toolbar that I have distributed to others as an excel add-in. I now
want to maintain that code and make a few changes. I originally made
the add-in by writing the macros for an individual worksheet. Adding in
a toolbar and then attaching that toolbar to the sheet. Then I saved
the whole thing as an add-in and distributed it to others' computers.
What is the best procedure for editing and maintaining the code? How
does one maintain the add-in code without the toolbars refering to old
versions of the program? Can I just edit the code in the VB editor and
the toolbars will refer to the correct version? People would then
install the new versions of the the add-in? Thanks. -Abe
 
Abe, This is how I create Toolbars for my addins:
This in its own module:

Option Explicit

Function CreateMenuBar(Name As String) As Variant

'*******************************************************'
'This procedure creates a menu bar for executing macros'
'*******************************************************'

Dim GenMacroBar As CommandBar
Dim BarControl As CommandBarButton
On Error Resume Next
Application.CommandBars(Name).Delete
On Error GoTo 0
Set GenMacroBar = Application.CommandBars.Add(Name)

'Button 1: Get CSV DATA
Set BarControl = GenMacroBar.Controls.Add()
BarControl.Style = msoButtonIconAndCaption
BarControl.Caption = "Get CSV Data"
BarControl.TooltipText = "Gets CSV Data"
BarControl.OnAction = "Get_csv_data"
BarControl.Picture = UserForm2.Image1.Picture

' Button 2: Hide/Show Columns
Set BarControl = GenMacroBar.Controls.Add()
BarControl.Style = msoButtonIconAndCaption
BarControl.Caption = "Hide/Show Columns"
BarControl.TooltipText = "Hides or Shows Columns"
BarControl.OnAction = "HideColumns"
BarControl.Picture = UserForm2.Image2.Picture

'Button 3: Get CSV DATA2
Set BarControl = GenMacroBar.Controls.Add()
BarControl.Style = msoButtonIconAndCaption
BarControl.Caption = "Get CSV Data"
BarControl.TooltipText = "Gets CSV Data2"
BarControl.OnAction = "Get_csv_data2"
BarControl.Picture = UserForm2.Image1.Picture

Application.CommandBars(Name).Position = msoBarTop
Application.CommandBars(Name).Visible = True

End Function

This is in the ThisWorkbook section:

Private Sub Workbook_AddinUninstall()
Application.CommandBars("CSV Macros").Delete
End Sub

Private Sub Workbook_Open()
Dim Found As Boolean
Dim cb As CommandBar
Found = False
For Each cb In Application.CommandBars
If cb.Name = "CSV Macros" Then Found = True
Next
If Found = False Then CreateMenuBar ("CSV Macros")
End Sub

Does that type of solution help you any?

Charles
 
Hi Charles-- so basically you are telling me to suck it up and figure
out how to add my toolbars programmatically? That way I don't have to
deal with thinking about Excel's processes for attaching toolbars to
worksheets + macros ect. I think this is good (if perhaps somewhat
labor intensive, in the short term) advice. Thanks. -Abe
 
Not entirely correct. Take this chunk of the code for example:
Set GenMacroBar = Application.CommandBars("AbesToolbar")
'Button 1: Get CSV DATA
Set BarControl = GenMacroBar.Controls("AbesButton")
BarControl.Style = msoButtonIconAndCaption
BarControl.Caption = "Get CSV Data"
BarControl.TooltipText = "Gets CSV Data"
BarControl.OnAction = "Get_csv_data"
BarControl.Picture = UserForm2.Image1.Picture

If you set GenMacroBar to your toolbar and BarControl to your button
then you can change all aspects of the existing toolbar
programatically. Is that something that will help?

Charles
 
Back
Top