Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toolbar Not Creation Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toolbar Not Creation Problem
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 jfcby wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Opinion on Toolbar Creation | Excel Programming | |||
Problem with function creation | Excel Programming | |||
Formula Creation via Add-in problem | Excel Programming | |||
Problem with chart creation | Excel Discussion (Misc queries) | |||
clear clipboard after Add-ins Toolbar creation | Excel Programming |