personal.xls already open

[Guest]

This is a problem that continues to pague us. I have serached and there is
only one copy of personal.xls and its located in the user start-up directory
along with a custom toolbarxlb file. When ever we try and run the macros from
a toolbar button we get the error message that Personal.xls is already open.
I have duplicated this on seveeral computer and its only happening on 2 of
them.

I'm stuck and hopefully some one can help.

 

[Dave Peterson]

The toolbar button that you click is trying to point to the personal.xls file?

If that's what you mean, my bet is that the button is pointing to a different
personal.xls in a different location.

If you reassign the button to the personal.xls in your XLStart folder, things
will get back to normal.

I don't have any buttons that call macros in other workbooks, but when I add a
toolbar, I assign the button to the macro in that workbook.

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)

 

[Bernie Deitrick]

Irene,

On the offending machines, open a new workbook, put the macro below into a codemodule, and run it.
Then select the good path, and the bad path from the list that is generated. It should fix it in
one pass, unless there are multiple bad paths to Personal.xls files.

HTH,
Bernie
MS Excel MVP

Sub FixUserDefinedButtonPath()
Dim CmdBar As CommandBar
Dim myRow As Long
Dim i As Long
Dim GoodPath As String
Dim BadPath As String

On Error GoTo ErrorReading1:

myRow = 1

For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
If InStr(1, CmdBar.Controls(i).OnAction, "Personal.xls") > 0 Then
Cells(myRow, 1).Value = CmdBar.Controls(i).OnAction
myRow = myRow + 1
End If
End If
ErrorReading1:
Next i
Next CmdBar

BadPath = Application.InputBox("Select a cell with the bad path", , , , , , , 8)
GoodPath = Application.InputBox("Select a cell with the good path", , , , , , , 8)

BadPath = Left(BadPath, InStrRev(BadPath, "\"))
GoodPath = Left(GoodPath, InStrRev(GoodPath, "\"))

MsgBox "Good path is " & GoodPath
MsgBox "Bad path is " & BadPath

On Error GoTo ErrorReading2:

If MsgBox("Are you sure you want to replace " & Chr(10) & BadPath _
& Chr(10) & "with" & Chr(10) & BadPath & "?", vbYesNo) = vbYes Then
For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
If InStr(1, CmdBar.Controls(i).OnAction, BadPath) > 0 Then
CmdBar.Controls(i).OnAction = Replace(CmdBar.Controls(i).OnAction, _
BadPath, GoodPath)
End If
End If
ErrorReading2:
Next i
Next CmdBar
End If

End Sub

 

[Guest]

I think you both got it! It's the toolbar!

I removed the tool bar and everything works just fine running from the
macro window. I just have to redo the toobar on the problem machines and it
should be back in business. Went to <Ctrl> X to automate for now. But this
may prove to be easier. One less file to worry about.

Thanks for the fast response