Problem w/custom Macro Buttons on move to Laptop

[Dennis]

In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
clicked executed the VBA code in my personal.xls file. The excel.xlb was
located in c:\Documents and Setting\comp1\Application Data\Excel and the
personal.xls file was located in "c:\Documents and Setting\comp1\Application
Data\Excel\Xlstart".

On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
the personnal.xls file into c:\Documents and Setting\Laptop\Application
Data\Excel\Xlstart

On the laptop when I bring Excel up the custom macro buttons are there
and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).

However when I click on a custom macro button I get the following error:
"A document with the name personal.xls is already open. You cannot open two
documents with the same name even if the documents are in different folders."

I did a search on the whole disk and there is only one file named personal.xls
and it is located as above.

If I click on Tools->Customize->right click the macro icon->assign Macro. The
Assign Macro Window appears with the macro name c:\Documents and
Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro

If I reassign the custom button to that same macro that shows up in the window
list below the Macro Name which shows the name Personal.XLS!mymacro, than that
custom macro button works fine after I close and reopen Excel while the other
custom buttons still won't work.

Since I have about 20 custom buttons assigned to 20 different VBA macros it
would be cumbersome to reassign each button.

Is there an easier way to redirect all the custom macro buttons to
Personal.XLS!?

Thanks for any help.

Dennis

 

[Dave Peterson]

First, back up files first!

But this seemed to work ok for me:

Option Explicit
Sub testme01()

Dim cBar As CommandBar
Dim cBarName As String
Dim ctrl As CommandBarControl
Dim newWkbk As Workbook
Dim newWkbkName As String
Dim ExclamePos As Long

newWkbkName = "Personal.xls"
cBarName = "yourtoolbarnamehere"

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

Set cBar = Nothing
On Error GoTo 0
Set cBar = Application.CommandBars(cBarName)
On Error GoTo 0
If cBar Is Nothing Then
MsgBox "Missing commandbar named: " & cBarName
Exit Sub
End If

For Each ctrl In cBar.Controls
If InStr(1, ctrl.OnAction, newWkbkName, 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
Next ctrl

End Sub

===========
But one way to avoid this problem is to build that toolbar when the workbook
opens (and delete it when the workbook closes.

This is how I do it:
http://groups.google.co.uk/[email protected]

 

[Dennis]

Thanks for the code Dave, it's neat!

Unfortunately my macro buttons are spread out over many different command bars
so it would be just as easy to reassign the 20 macros by leaving the
Tools->Customize window up and right click on each macro icon and then assign
Macro to the Personal.xls!mymacro in the macro list box.

MS should have an easier way to do this.

Dennis

 

[Dave Peterson]

Maybe just cycling through all the commandbars would help:

Option Explicit
Sub testme01()

Dim cBar As CommandBar
Dim ctrl As CommandBarControl
Dim newWkbk As Workbook
Dim newWkbkName As String
Dim ExclamePos As Long

newWkbkName = "Personal.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, newWkbkName, 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

==========
But I think the easiest solution would be to create the toolbars on the fly.
Then you won't have to go through the next time you upgrade.

 

[Dennis]

Thanks again Dave. This code did it for all my macros.

Dennis

 

[Dave Peterson]

It may have worked, but I'd still look at building the toolbar on the fly. It
really isn't that difficult and makes a lot simpler.

(Well, not simpler than being done--but you know what I mean <vbg>.)