[Excelfan]

OS: Windows xp professional sp3
I use excel 2007 workbooks with macros & macros are saved with the workbooks. After a workbook with macros is closed, the macros still show in VBA editor. And if the same work book is opened again, another set of same macros is shown in VBA editor. This also slows down the computer.
If the excel itself is closed after the workbook is closed & excel is restarted, the macros of closed workbook do not show in VBA editor.

[Zack Barresse]

Hi there, and welcome to the board!

Do you have the Project Explorer open in the VBE (Ctrl + R)? What is the bold project name of the workbook? And after it closes, what is the name? Are you sure you don't have your code in another workbook, i.e. Personal workbook?

[Excelfan]

I have just started writing macros. All my workbooks are for my personal use. I don't know what do you mean by "Do you have the Project Explorer open in the VBE (Ctrl + R)?"
The bold project name of work before & after closing the worokbook is the same 'Bills & Stock.xlsm'.
I don't have the code in another workbook.
This problem occurs with all workbooks with macros.

[Zack Barresse]

If you're in the VBE, Visual Basic Editor, whereas from Excel you hit Alt + F11, or went to the Developer tab (assuming you have it displayed) and clicked the Visual Basic button, from there hit Ctrl + R to show the Project Explorer (is generally docked to the left of the VBE). There will be listed all open workbooks, the names being in bold. If you're seeing the name of the workbook in there looks like it's already open. If the name is still listed there then the workbook would most presumably be open. Is there any workbook_close events in that workbook?

[Excelfan]

Thanks for quick response & tutorial.
Yes there is workbook_close event in the macros of all workbooks I have created.
1. if I just run Excel.exe (when by default Book1.xlsm opens), open VB editor (which shows the name 'Book1.xlsm' in Project Explorer) & then close Book1.xlsm (Excel & VBE still running), the name 'Book1.xlsm' still shows in the Project Explorer in VBE.
2. If I close Book1.xlsm without first opening the VBE, the name 'Book1.xlsm' does not show in the Project Explorer.

But in case of workbook created by me, in both cases (whether I close the workbook before or after opening VBE) the workbook's name & all modules show in the Project Explorer after closing the workbook.

[Zack Barresse]

Hmm, can you please post the workbook_close event code?

[Excelfan]

Sub H__ProtectSave()
'
' H__Protect_Save Macro
' Macro recorded 13/04/2005 by 1
'
'
Sheets("Summaries").Select
Dim DRange As Range
Set DRange = ActiveSheet.Range("D")
If Application.WorksheetFunction.CountIf(ActiveSheet _
.Range("B:B"), "Errors") = 0 Then
SheetCount = (Worksheets.Count)
SheetNum = 1
Do Until SheetNum = SheetCount + 1
Worksheets(SheetNum).Select
WorksheetName = Application.ActiveSheet.Name
If WorksheetName = "Summaries" Then
Sheets("Summaries").Unprotect
ActiveWorkbook.Names("BnS.Date.1.mth").RefersToR1C1 = _
"=EDATE(Summaries!R3C12,1)"
ActiveWorkbook.Names("BnS.Date.2.mths").RefersToR1C1 = _
"=EDATE(Summaries!R3C12,2)"
ActiveWorkbook.Names("BnS.Date.3.mths").RefersToR1C1 = _
"=EDATE(Summaries!R3C12,3)"
SheetNum = SheetNum + 1
Worksheets(SheetNum).Select
End If
ActiveSheet.Unprotect
ActiveWindow.LargeScroll ToRight:=-3
Application.Goto Reference:="R1C1"
Dim BRange As Range
Set BRange = ActiveSheet.Range("B:B")
RowAdd = "Y"
Do While RowAdd = "Y"
If Application.WorksheetFunction.CountIf(ActiveSheet _
.Range("B:B"), "") < 2 Then
RowAdd = "Y"
B__Add_10_Rows
Else
RowAdd = "N"
End If
Loop
Application.Goto Reference:="R1C1"
If WorksheetName <> "VT Finished Stock Summary" And WorksheetName <> "Buyerwise Receivable" And _
WorksheetName <> "Receivables within 1 month" And WorksheetName <> "Weaverwise Payable" And _
WorksheetName <> "Qualitywise Grey Stock" And WorksheetName <> "Processorwise Grey Stock" Then
JumpRow = Application.WorksheetFunction.Match _
("", ActiveSheet.Range("B:B"), 0)
ActiveCell.Offset(JumpRow - 1, 0).Range("A1").Select
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
SheetNum = SheetNum + 1
Loop
Sheets("Summaries").Select
ActiveSheet.Unprotect
Application.Goto Reference:="R2C6"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveWorkbook.Save
Else
Application.Goto Reference:="R1C4"
ActiveCell.Offset(0, 0).Columns("A:A").EntireColumn.Select
End If
End Sub


Sub C__ProtectSave_Close()
'
' C__Protect_Save_Close Macro
' Macro recorded 13/04/2005 by 1
'
'
H__ProtectSave
Dim Msg, Style
Msg = "Do you want to print reports?"
Style = vbYesNo + vbDefaultButton2
Response = MsgBox(Msg, Style)
If Response = vbYes Then
D__Print_Reports
End If
ActiveWorkbook.Close SaveChanges:=False
End Sub



workbook_close code is different in different workbooks.

[Zack Barresse]

Check the workbook on another computer. Does the same behavior apply? I don't see anything in there that looks like it'd stop it from closing. There are references calling procedures you have not posted though, so obviously we can't be sure.

[Excelfan]

The problem does not occur when these workbooks are opened on another computer.
It seems the problem has something to do with excel installed on my computer. I reinstalled excel but the problem persists.
Is there any option in excel where I have to change any setting?

[Zack Barresse]

Hmm, well that is interesting then. Narrowed down to your machine, and specific files. Can you test another arbitrary file (possibly just a newly created/saved file), and put some innocuous workbook_close routine in there, see if it has the same behavior? I wonder if it's just the close event. I've never heard of anything like this. I don't even know of anything leftover in the registry that will create this behavior, so I'm not sure if a reinstall, or even a 'deep cleaning' of the registry would help. Dunno, kinda lost on this one. Strange indeed. But I'm curious which events give you this behavior though. Can you test that out and let us know?