Custom Commandbars

Posted on by Dick Kusleika

Whenever I need to create a toolbar, I always do it in code. I started doing it that way because some of the tools were pointing to the wrong macros. Custom controls (tools) have an OnAction property. The OnAction property is a string that names the macro to be run when the control is clicked. Because it’s a string, it doesn’t change unless you change it.

The specific problem that I encountered was a toolbar that operated on a template. The controls all pointed to macros in the template, but when the template was saved with a different file name, the OnAction properties didn’t update. When those controls were clicked again, Excel would open the template file to get to the macros.

So I started creating custom commandbars in code, not just for templates, but for everything. Here’s the code I use to create my main toolbars. I call it from the Workbook_Open event of Personal.xls.

For built-in controls, I use an array and loop through the ID’s of the controls I want. It’s nicer than having a separate line for every control that I want to add. Once the built-in controls are added, I add several custom controls.

Sub Createcbs()
Dim aControls1 As Variant
Dim aControls2 As Variant
Dim i As Long
Dim cb As CommandBar

Const TBNAME1 As String = "Dick1"
Const TBNAME2 As String = "Dick2"

'store IDs for built-in controls
aControls1 = Array(2520, 23, 3, 2521, 109, 364, 128, 19, _
22, 369, 370, 372, 444, 226, 373, 374, _
375, 376, 377, 379, 380, 225, 2, 1695, 548)
aControls2 = Array(1728, 1731, 113, 114, 115, 401, 120, 122, _
121, 396, 397, 398, 203, 928, 443)

'delete commandbars if they exist
On Error Resume Next
With Application.CommandBars
.Item(TBNAME1).Delete
.Item(TBNAME2).Delete
End With
On Error GoTo 0

'create new commandbar
Set cb = Application.CommandBars.Add(Name:=TBNAME1, Position:=msoBarTop)

'add built-in controls
With cb
.Visible = True
For i = LBound(aControls1) To UBound(aControls1)
.Controls.Add ID:=aControls1(i), before:=i + 1
Next i
End With

'create new commandbar
Set cb = Application.CommandBars.Add(Name:=TBNAME2, Position:=msoBarTop)

'add built-in controls
With cb
.Visible = True
For i = LBound(aControls2) To UBound(aControls2)
.Controls.Add ID:=aControls2(i), before:=i + 1
Next i 'add custom controls

'The Print860 macro is located in Personal.xls
With .Controls.Add _
(Type:=msoControlButton, ID:=2521, before:=.Controls.Count + 1)

.Caption = "Print (Phaser860)"
.TooltipText = .Caption
.OnAction = "Print860"
End With
'I have custom button faces stored on sheet2. I copy them and use the
'.PasteFace method to but them on my custom controls
'The ShowTrim macro is in an add-in, so I fully qualify the macro name
'so it knows where to go.
With .Controls.Add(Type:=msoControlButton, before:=.Controls.Count + 1)
.Caption = "Trim Google URL"
.TooltipText = .Caption
.OnAction = "Myfunctions.xla!Module2.ShowTrim"
.Visible = True
Sheet2.Shapes("Picture 1").Copy
.PasteFace
End With
With .Controls.Add(Type:=msoControlButton, before:=.Controls.Count + 1)
.Caption = "Look up word"
.TooltipText = .Caption
.OnAction = "MyDict.xla!Module1.LookUpWord"
.Visible = True
Sheet2.Pictures("Picture 2").Copy
.PasteFace
End With
With .Controls.Add(Type:=msoControlButton, before:=.Controls.Count + 1)
.Caption = "Open Time Sheet"
.TooltipText = .Caption
.OnAction = "OpenTimeSheet"
.Visible = True
Sheet2.Pictures("Picture 5").Copy
.PasteFace
End With
End With
End Sub

toolbar

14 thoughts on “Custom Commandbars”

  1. I have two questions for you. Rather than putting the commandbar creation in your Personal.xls file, why not just include it in your add-in?

    Also, rather than deleting and recreating the toolbar if it already exists, why not just exit the creation sub so it’s not created again if it already exists?

    I have a slightly less complicated example here: http://www.xtremevbtalk.com/showpost.php?postid=335677&postcount=11

  2. Andy: Good questions, both. It’s in Personal.xls because I’m too lazy to move it. It started in personal.xls because that’s where my first custom button macro is (Print Phaser860). Then I made the google thread stripping userform and put it in an add-in. Then another add-in for my dictionary userform. So it’s all piece meal with no real planning. Someday I’ll clean up all those add-ins.

    I create/delete every time for the three or four times a year that I change the code. Otherwise, my changes would never happen. I also believe that the create/delete process keeps the xlb from getting corrupted/bloated. I don’t have evidence to support that belief, it’s just one facet of the wonderland in which I live. Also, if I ever move the code, I may *need* it to create/delete to avoid the template situation.

    That’s nice example code. I’ve never been to that site, but I saw it in my referrals log and browsed around a little bit today. There seems to be some pretty smart folks hanging around over there. Here’s how I would write that CheckForToolbar function, FYI

    Function CBExists(sCbName As String) As Boolean

        On Error Resume Next
            CBExists = Not Application.CommandBars(sCbName) Is Nothing
        On Error GoTo 0
        
    End Function

    Hey, I like that. I better add it to the code library.

    Thanks for reading. I’m always looking for relevent post ideas, so feel free to send them my way if you have any.

  3. Hi Dick.

    Since you mentioned .xlb bloat, I was wondering if you’ve found any solutions to it. I sure know how to cause it…

    The code below puts a clock onto my toolbar to update on the minute mark. (I used to work on a 12? laptop display, so full screen with no taskbar was the best way to show as much of the spreadsheet as possible. I still wanted a clock, so this code was born.) It autoruns from my personal macro book, so every time I go in or out of excel it restarts the process. I’ve noticed that with this happening, my .xlb grows by a few kb every time I quit and restart. If I take it out, the .xlb doesn’t change at all.
    I’ve also noticed that changing the toolbar makes the .xlb grow, regardless of whether I’m adding or deleting buttons or just tweaking it.

    Sub CreateMenu()
    On Error Resume Next
    If timemenuindex <> 0 Then CommandBars(1).Controls(timemenuindex).Delete
    Set newmenu = CommandBars(1).Controls.Add(Type:=msoControlButton, temporary:=True)
    timemenuindex = newmenu.Index
    aname = Format(Date, “mm/dd/yy”) & ” – ” & Format(Time, “H:MMAMPM”)
    With newmenu
    .Style = msoButtonCaption
    .Caption = aname
    .TooltipText = ActiveWorkbook.Name
    .OnAction = “SetTimer”
    .BeginGroup = True
    End With
    End Sub

    Sub SetTimer()
    Dim RoundDownTime As Date
    On Error Resume Next
    aname = Format(Date, “mmmm dd, yyyy”) & ” – ” & Format(Time, “H:MMAMPM”)
    newmenu.Caption = aname
    RoundDownTime = Format(Now, “mm/dd/yy H:mm:00 AMPM”)
    Application.OnTime RoundDownTime + TimeValue(“00:01:00?), “SetTimer”
    newmenu.TooltipText = ActiveWorkbook.Name
    End Sub

    My workaround so far has been to save a good .xlb and then use an old fashioned .bat file to overwrite my bloated .xlb on system startup. It works well enough, but makes any change to the toolbar a pain to implement.

    Any thoughts?

  4. Thanks for the tip! I tend to forget the old “error-trap negation” technique when I write functions, but it sure comes in handy to see if something exists or is open.

    Also, what is an XLB file?

  5. Oh, and as for the extremevb site, I’ve made quite a few references to J-Walk’s site (and his site is how I found this one).

    I also made reference to your Validation example that had used the Indirect function. That was pretty slick. I had always used macros to accomplish the same thing, but I really didn’t like that technique.

  6. rzf: My first thought is don’t ever use Excel to keep time.*g* But if you must, put that clock control on it’s own toolbar and delete/create it periodically, maybe ever hour. I don’t know if that will work, but it’s worth a try.

    Andy: XLB is where Excel stores your commandbars so that customizations are there when you exit and re-enter. There have been some problems with the xlb’s getting large. Do a search for *.xlb on your hard drive. My Excel.xlb in windows/application data/… is 12.4KB.

    I think that Validation post you made is what showed up on the referrers log. Thanks for the link, I got a few hits when you posted that. The only thing I don’t like about that Validation method is that the named ranges have to be exact.

  7. My book.xlt file is 124 KB… perhaps because I use a lot of temporary toolbars?

  8. My book.xlt file is 124 KB… perhaps because I use a lot of temporary toolbars?

  9. Now that I have a bigger monitor, I’ve given up that habit. However, you’ve got to admit changing your toolbar once a minute is a good way to stress the .xlb file. At it’s peak, my .xlb was over 1.2MB. It did make things a little slow, especially on the 233 Pentium was connected to the 12? screen. Now it’s around 10k.

  10. I have a question – I know this should be easy, and while I’m not a neophyte, I can’t come up with an easy way to do this – I need to take a lot of spreadsheets where my short range planning predecessors put multiple data categories in single cells – say last, first mi. – I need to strip the (in this case) last for column A, first for column B, and MI. for column C.

    Does anybody have a groovy script for this?

    Thanks,
    Ron

  11. Ron, you should be able to accomplish your task with various combinations of the Find, Left, Mid, and Right functions.

    Look in Excel’s help to see what I mean. When you’re first starting out, it would be easiest to do this in several steps. The key is how the items are currently separated (space, comma, etc.) or if they are all of a fixed length.

    For example:
    =Find(” “,A1) would find the placement of the first space in cell A1’s text. Let’s assume that that formula is in B1, and that A1 = “Hello World”. B1 would now contain the value 6.

    If C1 contains the formula =Left(A1,B1-1), then C1 would get everything to the left of the space from A1’s text (“Hello”).

  12. Thanks Andy, I’ve taken your lesson and expanded on it to make ends meet. Sorry for the delayed response – I had a pretty serious motoX accident. Just getting back up to speed, sort of.

  13. First of all, sorry for my poor english. I’m french, workong on Excel application in order to developp a software enabling the user to update a database with excel (import DB in Excel -> user modify the fields -> export Excel sheets in DB).

    my first question is about *.xlb file:
    How to not save in *.xlb the bars? (I disable some commandbar of Excel for my needs but I want it to be temporary.) Of course, I can enable them on the exit events of excel but I’d prefer not save the modifications. Do you know if a solution exist?

    second question about the line:
    .OnAction = “Myfunctions.xla!Module2.ShowTrim”

    You refer on a macro located in your xls workbook. But on the case you create commandbars from an addin or an other program in VB does the links work?

  14. Reeko,

    I don’t have an answer to your first question. I guess you could write code to save a copy of the .xlb file before you modify your commandbars.

    With your second question, the code you quote will work for the situation you describe. From Dick’s original post:

    “The ShowTrim macro is in an add-in, so I fully qualify the macro name so it knows where to go.”

    I hope that answers your question.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.