Attached Toolbars Macros

[Peter]

Excel 2003

I have a custom tool bar, this tool bar has several items, each item is
pointing to a VBA code. This tool bar is attached to the worksheet.

I've copied this spreadsheet to several computers. After I copied the
spreadsheet I decided to rename the spreadsheet, after I renamed the
spreadsheet the Toolbar Item's macros were still pointing to the old name, I
reasigned each tool bar item to the current spreadsheet, saved the
spreadsheet and everything was working on my computer, but when I re-copied
the new spreadsheet to other computers and I clicked on the Toolbar Item the
Items were still pointing to the macro in the original spreadsheet.

Where are the Toolbar definitions keept, because replacing the spreadsheet
does not change the Toolbar macro assignment?


Thank You


Peter

 

[Dave Peterson]

If you've attached the toolbar to the workbook (not a worksheet within the
workbook), then the assignment is kept in that workbook.

You can loop through each control and reassign the "onaction" to the new
workbook's name.

Saved from a previous post:

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Peter]

I don't understand how do you attach the toolbar to a worksheet. I can see
only one option to attach the toolbar.

I would like to create the tool bar through code, but I can't find an
example where you create a tool bar that has a dropdown box and each list
item has a VBA macro assigned to it.

 

[Dave Peterson]

You don't attach a toolbar to a worksheet. However, you can attach a toolbar to
a workbook. But I wouldn't do it.

I'm not sure how complex your toolbar is going to be, but maybe this will get
you started--or you can match merge with the code on Debra's site:

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant

Call RemoveMenubar

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "Caption1"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!mac1"
.Caption = "myMac1"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!mac2"
.Caption = "myMac2"
End With
End With

End With
End Sub
Sub ResetMenu()
oMenu.Delete
End Sub
Sub mac1()
MsgBox "mac1"
End Sub
Sub mac2()
MsgBox "mac2"
End Sub

 

[Peter]

Thank You, this is exactly what I needed!

You don't attach a toolbar to a worksheet. However, you can attach a
toolbar to
a workbook. But I wouldn't do it.

I'm not sure how complex your toolbar is going to be, but maybe this will
get
you started--or you can match merge with the code on Debra's site:

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant

Call RemoveMenubar

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "Caption1"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!mac1"
.Caption = "myMac1"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!mac2"
.Caption = "myMac2"
End With
End With

End With
End Sub
Sub ResetMenu()
oMenu.Delete
End Sub
Sub mac1()
MsgBox "mac1"
End Sub
Sub mac2()
MsgBox "mac2"
End Sub