Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why does macros assigned to a toolbar lose its link when saved as
I have a toolbar attached to my workbook and all the macros are saved in this
particular workbook. When I open this document on another computer it prompts for the old path. I then have to sit and re-assign each of the macros to the toolbar. Is there a way of preventing this? Help will be much appreciated |
#2
|
|||
|
|||
I wouldn't attach the toolbar to the workbook.
I'd build the toolbar on the fly. Here's how I do it: http://groups.google.co.uk/groups?th...5B41%40msn.com If you want to add items to the worksheet menu bar, you can use John Walkenbach's menumaker: http://j-walk.com/ss/excel/tips/tip53.htm Elsa wrote: I have a toolbar attached to my workbook and all the macros are saved in this particular workbook. When I open this document on another computer it prompts for the old path. I then have to sit and re-assign each of the macros to the toolbar. Is there a way of preventing this? Help will be much appreciated -- Dave Peterson |
#3
|
|||
|
|||
Why does macros assigned to a toolbar lose its link when saved
Thanks for your answer. It is very handy.
I created some custom buttons with the Excel macro facility. I am using differnt kinds of borders, thus the picture of the border would really be helpful, instead of displaying 1 Thick Sides 2 Thin Sides Is there no way of re-assigning the macros to an already existing custom toolbar? "Dave Peterson" wrote: I wouldn't attach the toolbar to the workbook. I'd build the toolbar on the fly. Here's how I do it: http://groups.google.co.uk/groups?th...5B41%40msn.com If you want to add items to the worksheet menu bar, you can use John Walkenbach's menumaker: http://j-walk.com/ss/excel/tips/tip53.htm Elsa wrote: I have a toolbar attached to my workbook and all the macros are saved in this particular workbook. When I open this document on another computer it prompts for the old path. I then have to sit and re-assign each of the macros to the toolbar. Is there a way of preventing this? Help will be much appreciated -- Dave Peterson |
#4
|
|||
|
|||
Why does macros assigned to a toolbar lose its link when saved
You could look for the old name and reassign the button using the new name:
Option Explicit Sub testme01() Dim cBar As CommandBar Dim ctrl As CommandBarControl Dim newWkbk As Workbook Dim newWkbkName As String Dim oldWkbkName As String Dim ExclamePos As Long oldWkbkName = "oldName.xls" newWkbkName = "newName.xls" Set newWkbk = Nothing On Error Resume Next Set newWkbk = Workbooks(newWkbkName) On Error GoTo 0 If newWkbk Is Nothing Then MsgBox "Please open the new " & newWkbkName & " file!" Exit Sub End If For Each cBar In Application.CommandBars For Each ctrl In cBar.Controls If ctrl.BuiltIn Then 'do nothing Else If InStr(1, ctrl.OnAction, oldWkbkName, vbTextCompare) 0 Then Debug.Print "----" & ctrl.Caption & "----" & vbLf _ & ctrl.OnAction ExclamePos = InStr(1, ctrl.OnAction, "!") If ExclamePos 0 Then ctrl.OnAction = newWkbk.Name _ & Mid(ctrl.OnAction, ExclamePos) End If Debug.Print ctrl.OnAction End If End If Next ctrl Next cBar End Sub Elsa wrote: Thanks for your answer. It is very handy. I created some custom buttons with the Excel macro facility. I am using differnt kinds of borders, thus the picture of the border would really be helpful, instead of displaying 1 Thick Sides 2 Thin Sides Is there no way of re-assigning the macros to an already existing custom toolbar? "Dave Peterson" wrote: I wouldn't attach the toolbar to the workbook. I'd build the toolbar on the fly. Here's how I do it: http://groups.google.co.uk/groups?th...5B41%40msn.com If you want to add items to the worksheet menu bar, you can use John Walkenbach's menumaker: http://j-walk.com/ss/excel/tips/tip53.htm Elsa wrote: I have a toolbar attached to my workbook and all the macros are saved in this particular workbook. When I open this document on another computer it prompts for the old path. I then have to sit and re-assign each of the macros to the toolbar. Is there a way of preventing this? Help will be much appreciated -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I display stop recording toolbar for macros | Excel Discussion (Misc queries) | |||
Toolbar problem | Excel Discussion (Misc queries) | |||
Custom toolbar and macros | Excel Discussion (Misc queries) | |||
Copying a workbook with custom toolbar assigned to a macro | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |