VBA Project

[Steve Peel]

I have written an extensive VBA project in Excel 2003 which my company uses
to track job and crew information. Recently, this project, which has been
used for the last year and a half, has been producing code errors. Today, it
wil not even open without erroring to the recover screen and the recovery has
lost the VBA project after recovery.
I have a feeling that this is related to Microsoft Updates.
Anyone having these problems?? Any Solutions??

 

[Norman Jones]

Hi Steve,

In order to facilitate usful responses, you
should provide detailed informaation about
the errors.

 

[Steve Peel]

Norman

On some of it I can't be more detailed because it doesn't give me the chance
to see the error, but the error log appears below....

Microsoft Office Excel File Repair Log

Errors were detected in file 'L:\DATA\210 Office\210 Weekly
Tracking\2008-5-31.xls'
The following is a list of repairs:

Lost Visual Basic project.
Lost ActiveX controls.

Additionally, I copied two sub-routines that have worked since I wrote them,
they are Called by "Worbook_Open". All of the sudden, it produces a run-time
error at the line I marked with (Error). If I stop the debugger and run the
procedure, it works fine but it will not work when called during the opening
of the workbook.

Sub TempName() 'From Module2.DeleteCrew() and Module2.AddCrew() and
Workbook_Open
Dim OldSheet As Excel.Worksheet
Dim n As Integer, t As Integer
Application.ScreenUpdating = False
t = 1
For n = 8 To 44
Set OldSheet = Sheets(n)
OldSheet.Name = "Temp" & t 'Rename Sheets a "Temp#" before they are
sorted
t = t + 1
Next n
Set OldSheet = Nothing
End Sub

Sub NewName() 'From Module2.DeleteCrew() and Module2.AddCrew() and
Workbook_Open
Dim TempSheet As Excel.Worksheet
Dim n As Integer, t As Integer
Dim ShtName As String
Application.ScreenUpdating = False
t = 1
For n = 8 To 44 'Run through each crew sheet
Set TempSheet = Sheets(n)
ShtName = "Temp" & t
(Error)TempSheet.Name = Sheets(ShtName).Cells(2, 3) 'Rename sheets after
they are sorted
t = t + 1 'from "Temp#" to actual
crew names stored in sheets
Next n
Set TempSheet = Nothing 'Clear Variable
End Sub