Help with Add ins (.xla) not showing

[PGrinstead]

Hi all.

I'm a total newbie with regards to VBA, so I've struggled through to make a custom toolbar for usage with our team at work. It was a very simple selection of macros assigned to clickable buttons.

I saved it as an .xla, and when I go into Excel (2003 btw), I have selected the option as an add-in, from Tools -> Add Ins -> (name).

However, when I right click on my toolbar area to select the custom toolbar as an option, it is not there to open.

When I hit Alt+F11, the VBA is there under VBA Project(name.xla).

Why is the toolbar itself not showing up, even after I've selected it as an add-in?

As I said, I'm just dabbling in VBA, having pieced it together from message boards and reverse engineering. I'm very basic (no pun intended!) so if you could keep helpful replies very step-by-step and basic, I'd appreciate it.

Thanks,
Phil

 

[RoryA]

Is your toolbar created in code, or did you create it manually?

 

[PGrinstead]

Hi rorya.

I created the toolbar by putting the vba into separate sections, and then attaching each section to a custom macro button in a custom toolbar.

I have all the vba code in a long sheet also, with 12 excel 'functions' (create highlighted area into formatted table / add the iserror fuction to highlighted formulae etc) if you could walk me through an easier way to create a shareable toolbar.

Thanks,
Phil

 

[RoryA]

I always prefer to create toolbars in code. For example:

Option Explicit
Public Const CBAR_NAME As String = "CalcMode"
Public Sub AddMenuItem()
   Dim ctl As CommandBarControl, cbr As CommandBar
   
   ' delete any existing copy of the toolbar
   DeleteMenuItem
   
   ' add new one
   Set cbr = Application.CommandBars.Add(Name:=CBAR_NAME, Temporary:=True)
   ' get any stored position settings
   With cbr
      .Position = GetSetting(CBAR_NAME, "Menu", "Position", msoBarFloating)
      .RowIndex = GetSetting(CBAR_NAME, "Menu", "RowIndex", msoBarRowLast)
      .Left = GetSetting(CBAR_NAME, "Menu", "RowIndex", 0)
   End With
   
   ' add a button to the toolbar
   Set ctl = cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
   ' set its properties
   With ctl
      .Caption = "Calculation mode is: " & GetCalcMode
      .Style = msoButtonCaption
      .OnAction = "ChangeCalcMode"
      .Tag = CBAR_NAME
   End With
   
   ' show toolbar
   With cbr
      .Enabled = True
      .Visible = True
   End With
End Sub
Public Sub DeleteMenuItem()
   ' delete any existing copy of the toolbar
   On Error Resume Next
   Application.CommandBars(CBAR_NAME).Delete
End Sub

and this code is then called when the workbook opens and closes using the events in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    With Application.CommandBars(CBAR_NAME)
        SaveSetting CBAR_NAME, "Menu", "Position", .Position
        SaveSetting CBAR_NAME, "Menu", "RowIndex", .RowIndex
        SaveSetting CBAR_NAME, "Menu", "Left", .Left
    End With
    DeleteMenuItem
End Sub
Private Sub Workbook_Open()
  AddMenuItem
End Sub

 

[PGrinstead]

rorya,

So the code you included in the first quote, can I add that to the start of my vba code, and it will automatically open the assortment of macros on a toolbar?

I think the thing where I'm stumbling is this:

How do I change a string of vba commands to a toolbar? Is the way I did it, manually attaching them all to buttons, the correct way to create and share a .xla command?

Or is there something I'm missing. I looked for a step-by-step guide, but all the topics I found assume a (what I consider!) advanced level of vba that I just totally lack at the moment!

Thanks for your help ongoing,
Phil

 

[RoryA]

The first bit of code creates a new toolbar and adds a button to it, then assigns a macro to that button. You would need to alter that code to create all the buttons you want and assign the relevant routines to each button.
The second code is what calls the first code automatically when the workbook is opened.

You may also want to have a look at John Walkenbach's table-based menu builder here.

 

[PGrinstead]

I'll give the table maker a try when I get some time over the next day or so, thanks for the link!

Back to original question, is the correct way to share this among the team to save the bar as an .xla and pop it in the add-ins folder? I'm at a bit of a loss as to how I save/share a toolbar, as I don't quite understand the whole "work in vba, close it, save the workbook as an .xla and then the toolbar always exists in the background" part.

Any chance of a brief idiot's guide step by step for sharing a custom toolbar?

Thanks again,
Phil

 

[RoryA]

If you use code such as the example I posted, all you need to do is save the workbook as an add-in. Whenever it is opened on any machine, it will create the toolbar you need automatically.
You might also want to have a read through Jan Karel's great article here.