Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 12th 05, 07:29 AM
Elsa
 
Posts: n/a
Default 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   Report Post  
Old October 12th 05, 03:10 PM
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Old October 18th 05, 09:26 PM
Elsa
 
Posts: n/a
Default 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   Report Post  
Old October 18th 05, 09:48 PM
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I display stop recording toolbar for macros Randy Excel Discussion (Misc queries) 2 July 8th 05 08:56 PM
Toolbar problem Anthony Excel Discussion (Misc queries) 1 June 1st 05 01:05 AM
Custom toolbar and macros MD Excel Discussion (Misc queries) 2 May 10th 05 05:31 PM
Copying a workbook with custom toolbar assigned to a macro Matt W Excel Discussion (Misc queries) 1 February 4th 05 11:46 PM
Stubborn toolbars in Excel 007 Excel Discussion (Misc queries) 9 December 11th 04 03:02 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017