Toolbar Not Creation Problem

[jfcby]

Hello,

I trying to create a toolbar with popups & buttons. I using the macro
below to:

1. Create the toolbar
2. Add Popup menus with buttons
3. Add more buttons to hte main toolbar

My macro will not all multiple buttons to the Popup menus. How can I
add multiple buttons the the popup menus without using several with
statements?

<MACRO CODE BEGIN>

Rem ****BEGIN**** Command / Tool Bar ************
Function TBN() As Variant
'Tool Bar Name
TBN = "ToolBar Example Test"
End Function
Sub CreateOtherCommandBar()
'
Dim i As Variant, j As Variant
Dim cPopup(1 To 9)
Dim macs(1 To 9, 1 To 9), caps(1 To 9, 1 To 9), tips(1 To 9, 1 To 9)

'Main Toolbar Control Popup Menus
cPopup(1) = "Column Options"
cPopup(2) = "Row Options"
cPopup(3) = "Sort Options"

'Main & Sub Toolbar Buttons
'Column Options
macs(1, 1) = "CTB1"
macs(1, 2) = "CTB2"
macs(1, 3) = "CTB3"
'Row Options
macs(2, 1) = "CTB1"
macs(2, 2) = "CTB2"
'Sort Options
macs(3, 1) = "CTB1"
macs(3, 2) = "CTB1"
'Main Toolbar Button Macros
macs(4, 1) = "MTB1"
macs(4, 2) = "MTB2"

'Column Options
caps(1, 1) = "H/U Col"
caps(1, 2) = "I/D Col"
caps(1, 3) = "Move Col"
'Row options
caps(2, 1) = "H/U Row"
caps(2, 2) = "I/D Row"
'Sort Options
caps(3, 1) = "Sort Asc"
caps(3, 1) = "Sort Dsc"
'Main Toolbar Button Captions
caps(4, 1) = "MTB1"
caps(4, 2) = "MTB2"

'Column Tips
tips(1, 1) = "H/U Col"
tips(1, 2) = "I/D Col"
tips(1, 3) = "Move Col"
'Row Tips
tips(2, 1) = "H/U Col"
tips(2, 2) = "I/D Col"
tips(2, 3) = "Move Col"
'Sort tips
tips(3, 1) = "H/U Col"
tips(3, 2) = "I/D Col"
tips(3, 3) = "Move Col"
'Main Toolbar Button Tips
tips(4, 1) = "H/U Col"
tips(4, 2) = "I/D Col"
tips(4, 3) = "Move Col"


'Delete Toolbar
DeleteOtherCommandBar

'Create Toolbar
With Application.CommandBars.Add
.Name = TBN
.Visible = True
For i = LBound(caps, 1) To UBound(caps, 1)
For j = LBound(caps, 2) To UBound(caps, 2)
If caps(i, j) <> "" Then

'Create Popup Menus
With .Controls.Add(Type:=msoControlPopup)
If j = i Then .Caption = cPopup(i)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & macs(i, j)
.cPopup(i).Caption = caps(i, j)
.cPopup(i).TooltipText = tips(i, j)
End With
End With

'Create Buttons
If i >= 4 Then
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & macs(i, j)
.Caption = caps(i, j)
.TooltipText = tips(i, j)
.Style = msoButtonIconAndCaption
End With
End If
End If
Next j
Next i
End With
End Sub

Sub DeleteOtherCommandBar()
'Toolbar name is defined above in Function TBN _
& can be changed
On Error Resume Next
Application.CommandBars(TBN).Delete
On Error GoTo 0
End Sub

<MACRO CODE END>

Thank you for your help,
jfcby

 

[Dave Peterson]

How about creating a different structure to hold the pieces for each
macro/caption/tip.

Option Explicit
Const TBN As String = "ToolBar Example Test"
Sub CreateOtherCommandBar()

Dim i As Long
Dim j As Long
Dim myCommandBar As CommandBar
Dim myCtrl As Object
Dim myDD As Object

Dim cPopups As Variant
cPopups = Array(Array("Column Options", "ColTips"), _
Array("Row Options", "RowTips"), _
Array("Sort Options", "SortTips"))

Dim MacCapTip As Variant
MacCapTip = Array(Array(Array("c1Mac1", "c1cap1", "c1tip1"), _
Array("c1mac2", "c1Cap2", "c1tip2"), _
Array("c1mac3", "c1Cap3", "c1Tip3")), _
_
Array(Array("c2Mac1", "c2cap1", "c2tip1"), _
Array("c2mac2", "c2Cap2", "c2tip2")), _
_
Array(Array("c3Mac1", "c3cap1", "c3tip1"), _
Array("c3mac2", "c3Cap2", "c3tip2"), _
Array("c3mac3", "c3cap3", "c3Tip3")))

Dim myButtons(1 To 2) As Variant
myButtons(1) = Array("Mac1", "cap1", "tip1")
myButtons(2) = Array("Mac2", "cap2", "tip2")

'Delete Toolbar
DeleteOtherCommandBar

'Create Toolbar
Set myCommandBar = Application.CommandBars.Add
With myCommandBar
.Name = TBN
.Visible = True
For i = LBound(cPopups) To UBound(cPopups)
Set myCtrl = .Controls.Add(Type:=msoControlPopup)
With myCtrl
.Caption = cPopups(i)(0)
.TooltipText = cPopups(i)(1)
For j = LBound(MacCapTip(i)) To UBound(MacCapTip(i))
Set myDD = .Controls.Add(Type:=msoControlButton)
myDD.OnAction = "'" & ThisWorkbook.Name & "'!" _
& MacCapTip(i)(j)(0)
myDD.Caption = MacCapTip(i)(j)(1)
myDD.TooltipText = MacCapTip(i)(j)(2)
Next j
End With
Next i

For i = LBound(myButtons) To UBound(myButtons)
Set myCtrl = .Controls.Add(Type:=msoControlButton)
myCtrl.Style = msoButtonCaption
myCtrl.OnAction = myButtons(i)(0)
myCtrl.Caption = myButtons(i)(1)
myCtrl.TooltipText = myButtons(i)(2)
Next i
End With
End Sub
Sub DeleteOtherCommandBar()
'Toolbar name is defined above in Function TBN _
& can be changed
On Error Resume Next
Application.CommandBars(TBN).Delete
On Error GoTo 0
End Sub





This portion creates the list of captions for the popup.

Dim cPopups As Variant
cPopups = Array(Array("Column Options", "ColTips"), _
Array("Row Options", "RowTips"), _
Array("Sort Options", "SortTips"))

It consists of 3 arrays contianed in a larger array. Each line represents a
popup--first the caption, then the tooltiptext.

========

This portion is a giant array.

Dim MacCapTip As Variant
MacCapTip = Array(Array(Array("c1Mac1", "c1cap1", "c1tip1"), _
Array("c1mac2", "c1Cap2", "c1tip2"), _
Array("c1mac3", "c1Cap3", "c1Tip3")), _
_
Array(Array("c2Mac1", "c2cap1", "c2tip1"), _
Array("c2mac2", "c2Cap2", "c2tip2")), _
_
Array(Array("c3Mac1", "c3cap1", "c3tip1"), _
Array("c3mac2", "c3Cap2", "c3tip2"), _
Array("c3mac3", "c3cap3", "c3Tip3")))

Each line represents the macro name, caption, and tooltip for one item in the
popup.

Then each popup needs as many of these as you want. 3 for the column, 2 for the
row and 3 for the sort.

And the whole thing is inside another array.

Remember that each macro is on one line--the macro name, caption and tooltip.
It shouldn't be too difficult to update this for your real stuff.

Be aware that the tooltiptext for these items didn't show up for me. I could
set them, but I couldn't see them when I let the cursor linger over them.

=================
And I did the buttons separately.

Dim myButtons(1 To 2) As Variant
myButtons(1) = Array("Mac1", "cap1", "tip1")
myButtons(2) = Array("Mac2", "cap2", "tip2")

This is much more straight forward. But it's the same thing--one line per
macro.

=========
I also removed the TBN function and just used a constant. It seemed a little
more straight forward.

============
There are other ways to do this kind of stuff.

John Walkenbach has a similar routine, but he stores all this info in a
worksheet. It makes it easier for updating.

http://j-walk.com/ss/excel/tips/tip53.htm