Docking a Dynamic toolbar (Excel 2000/VBA)

[ChrisGreaves]

A web search for "excel build command bar mvp pearson" (without quotes) turns up some dandy VBA code for establishing toolbars on-the-fly from Excel VBA. I use the technique frequently, because it makes sense - for each invented macro - to clone a line of VBA code instead of going through the labor of dragging a smiley face, r/c, assign macro, r/c assign button and so on.

This morning I got fed up with the toolbar plonking itself down mid-screen in my worksheet, so:

Code: Select all

Function AlignMyBar(oBar As CommandBar)
    Dim lng As Long
    For lng = 1 To Application.CommandBars.Count
        If Application.CommandBars(lng).Visible Then
            If Application.CommandBars(lng).Name = "Standard" Then
                oBar.RowIndex = Application.CommandBars(lng).RowIndex
                oBar.Left = 0
                ' Exit For
            Else
            End If
        Else
        End If
    Next lng
End Function

[HansV]

Hi Chris,

Your code will put the toolbar in a specific position (that of the Standard toolbar). If you merely want to dock the toolbar, the following line should be sufficient:

oBar.Position = msoBarTop

[ChrisGreaves]

oBar.Position = msoBarTop

Thanks, Hans.
I started off with msoBarTop, but didn't like seeing the 1-inch toolbar sitting on a line by its own.
My Word Under-The-Hood utility has a macro BestFitToolbarsByName which uses a best-fit algorithm to maximize the use of space.
I could bring that into Excel; but for now I have just a few one-inch toolbar menus, so slotting them on the same row as "Standard", and pushing it rightwards suffices for now.

[HansV]

OK, that makes sense.

[ChrisGreaves]

OK, that makes sense.

Well!
Of course it makes sense!
I said it, didn't I?

Here is what it looks like, top-left corner with part of the menu extended:

5.JPG

P.S. Bonus Points for working out why "chocciebar" (Swiss members of the lounge need not apply)

[HansV]

The toolbar is named Toblerone?

[ChrisGreaves]

The toolbar is named Toblerone?

Spoiler

Nestled

It's a bit of a mean play on the e-acute.
Or is it e-grave?

[Jan Karel Pieterse]

I would write the current position of your bar to the registry so next time Excel starts, it ends up at the same location. I myself tend to re-position toolbars and really dislike third-party toolbars who insist on coming up in a specific location. SO I use something these two subs:

Code: Select all

Sub StoreBarPostion(oBar As CommandBar)
    If oBar Is Nothing Then Exit Sub
    With oBar
        SaveSetting gsAppRegKey, "Settings", "ToolbarPosition", CStr(.Position)
        SaveSetting gsAppRegKey, "Settings", "ToolbarRowIndex", CStr(.RowIndex)
        SaveSetting gsAppRegKey, "Settings", "ToolbarLeft", CStr(.Left)
        SaveSetting gsAppRegKey, "Settings", "ToolbarTop", CStr(.Top)
    End With
End Sub

Sub GetBarPosition(oBar As CommandBar)
    If oBar Is Nothing Then Exit Sub
    With oBar
        .Position = CLng(GetSetting(gsAppRegKey, "Settings", "ToolbarPosition", msoBarTop))
        .RowIndex = CLng(GetSetting(gsAppRegKey, "Settings", "ToolbarRowIndex"))
        .Left = CDbl(GetSetting(gsAppRegKey, "Settings", "ToolbarLeft"))
        .Top = CDbl(GetSetting(gsAppRegKey, "Settings", "ToolbarTop"))
    End With
End Sub

[ChrisGreaves]

... re-position toolbars

Jan Karel: as the youngsters say "You ROCK!"

Code: Select all

Dim gsAppRegKey As String
gsAppRegKey = ThisWorkbook.VBProject.Name

or similar.

[ChrisGreaves]

... establishing toolbars on-the-fly from Excel VBA.

An unexpected bonus:
Moving a procedure from one module to another does NOT corrupt the toolbar, as it would in Word.
Rebuilding the toolbar on the fly seems to mean that just the macro name, regardless of the module, is hooked on to the toolbar menu controls.
(In Word, where i dragged macros to toolbars, I had to be careful about shuffling procedures between modules)

[Jan Karel Pieterse]

What you do need to take into account is that when multiple addins are loading and one happens to have a button which has the same OnAction Sub name as another, both toolbar buttons call the same sub in the same add-in (the last one loaded IIRC). Best is to prepend the OnAction property with the addin's name:

.OnAction="'" & Thisworkbook.Name & "'!SubName"

[ChrisGreaves]

What you do need to take into account ...

Thanks Jan Karel.
You make me look good!

[Jan Karel Pieterse]

Well, lets just say I've been surprised by what happened when a user clicked one of "my" buttons. Unpleasantly surprised.