Solved: Mysterious macro running on Excel file close?

[Dannoo]

Hello forum!

Each time I close this Excel file an I-alert message appears saying:

"Cannot find 'D:\UserData\Classeur1.xls'!PCDOCSFileClose, which has been assigned to run each time [Project EXO]Summary is closed. Continuing could cause errors. Cancel closing [Project EXO]Summary? Yes No "

I press No and file closes. I can then re-open it just fine..

I have imported some sheets from a file created by another person, and I suspect that the problem comes from there. So far, I went into the VBA Project explorer view and deleted all empty modules and forms, and for each Sheet I did the Ctrl-A + Delete cleanup.

Lower-beginner level here so bear with me.

Thanks a lot in advance!

Dan.

[mancubus]

wellcome.

hit alt+F11 to open vbe. double click worksheet names (left) and check if some codes appear in the window (right).

[mdmackillop]

Possibly something in Personal.xls or an add-in?
Can you clear any data and post your workbook? (Manage Attachments in Go Advanced Reply section.)

[Dannoo]

Thanks for the quick reply!

Oh well. It looks like there's a 1 meg limit to it, so I zipped it. I send you an empty version, but you will still get that message when you close the file. and then if you find the cause fix it I can replicate the same in the original file.

Also, I just noticed that there is a buch of unknown lists - that is why the file is so big even without any obvious content in it.. Do you know of any VBA code to remove ALL lists en-masse?

Thanks!
Dan

Possibly something in Personal.xls or an add-in?
Can you clear any data and post your workbook? (Manage Attachments in Go Advanced Reply section.)

[mdmackillop]

Running this in new sheet will list all the names + references
Searching for PCDOCSFileClose finds a name Auto_Close which is triggering the message.

[VBA]Sub Test()
For Each n In ActiveWorkbook.Names
i = i + 1
Cells(i, 1) = n.Name
Cells(i, 6) = "x" & n.RefersTo
Next
End Sub
[/VBA]

[mdmackillop]

Re your second point, the code I gave can be amended to check/delete names. Never seen so many in a workbook! But it could cause problems. Only try it on a copy.

[Dannoo]

Beautiful !
I got rid of it.
Thanks a TON! Great.

... um. how do you actually amend that code to do the deleting??
(blushhh)

anyways, thanks again!

[mdmackillop]

Looking at your file further, I don't see Auto_Close showing in the list of names. Using JKP's excellent Name Manager does show this name and will help you delete it.

[Dannoo]

perfect !
great work!!!

thanks
Dan.

[mdmackillop]

There are some hidden sheets in your workbook. Here are a few macros I made up to see what was going on. There are some names though, which won't delete by code,
Interesting problem

[VBA]Option Explicit

Sub ListNames()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long

Columns("A:G").Clear
With ActiveWorkbook
For Each n In .Names
i = i + 1
Cells(i, 1) = n.Name
Cells(i, 6) = "x=" & n.RefersTo
Next
End With
End Sub

Sub ShowAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next
End Sub

Sub UnHideSheets()
Dim sh
For Each sh In Sheets
sh.Visible = True
Next
End Sub

Sub DelRefErrors()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long
On Error Resume Next
With ActiveWorkbook
For Each n In .Names
txt = n.RefersTo
If InStr(1, txt, "#REF") > 0 Then
n.Delete
x = x + 1
End If
Next
End With
MsgBox x & " #REF errors deleted"
End Sub

Sub DelAllNames()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long

On Error Resume Next
With ActiveWorkbook
For Each n In .Names
n.Delete
Next
End With
End Sub

[/VBA]

[Jan Karel Pieterse]

This is one of those examples of files with a very long editing history. This file seems to have a Lotus 123 origin too, given some of the range names.
If you switch your workbook to R1C1 mode, you will be prompted to fix a number of range names, which is very time consuming.
Using my remediation tool I could fix almost all names and subsequently remove all but three using my name manager. Of the remaining three I could get rid of two using Excel's Insert name dialog. One was left behind, a name named r (single character), which is illegal because of the R1C1 reference mode. Toggling R1C1 made Excel prompt for a replacement name for r, which I could then delete.
Your workbook needs work!