B
Brandon White
I have a spreadsheet in use across Europe (US Developer), and in the code
that cleans up the macro (closes files that it opened, deletes the toolbar
entry, etc), Excel will crash. The spreadsheet is already closed, the Excel
screen is blank, and then "boom".
The thing that is weird is that this only happens in CERTAIN LANGUAGES. US
English doesn't cause an error; but German, Norwegian, and Swedish do
(perhaps others, but those are the ones that are reported in).
To net out what is happening, certain files (partsfile and assume) get
closed if they are the only programs open that use this data, and the
user-created menu bar (not toolbar) gets deleted as well. The funny thing is
that this code worked until recently - I have rebuilt files, I have copied
and pasted into an old version that works, but I am getting anxious users
across Europe. The crash is an Excel error - it is in vbe6.dll. I can
reproduce it when I set my PC to German - but not US English, and I am using
Office 2003 SP3, vbe6 version = 6.5.10.24
I'm running out of ideas - have searched everywhere online, but am stumped.
The program is closed, the files are closed, and then I get the error. Would
greatly appreciate any help on this - the offending routine (from
ThisWorkbook) is pasted below, or so I think. A routine inside the program
did the close, if that matters.
Thanks,
Brandon
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim PartsFile As String
Dim PRP As String
PRP = fn_Roadmap() ' Don't close the wrong file
ActiveWorkbook.SaveLinkValues = False ' Don't save link values to prevent
error message
Call DeleteMenu ' Delete the menu from this PRP
If fn_Roadmaps = 1 Then ' If > 1 then don't close files and delete menus
If fn_PriceCalc() <> "" Then ' Is PriceCalc open? Don't close files
Else
If fn_ISFILEOPEN(Assume) Then
If Workbooks(Assume).Windows.Item(1).Visible = False Then
Workbooks(Assume).Windows.Item(1).Visible = True
If fn_ReadOnlyWB(Assume) Then Workbooks(Assume).Close
Savechanges:=False ' if opened read only close assumptions file
End If
If Worksheets("Data Sheet").Range("D8").Value <> "Canada" Then PartsFile
= "EMParts.xls" Else PartsFile = "CAParts.xls"
If fn_ISFILEOPEN(PartsFile) Then
If Workbooks(PartsFile).Windows.Item(1).Visible = False Then
Workbooks(PartsFile).Windows.Item(1).Visible = True
If fn_ReadOnlyWB(PartsFile) Then Workbooks(PartsFile).Close
Savechanges:=False ' if opened read only close assumptions file
End If
If fn_ISFILEOPEN("EMPartsSales.xls") Then
Workbooks("EMPartsSales.xls").Close Savechanges:=False
If fn_ISFILEOPEN("CAPartsSales.xls") Then
Workbooks("CAPartsSales.xls").Close Savechanges:=False
End If
End If
If fn_PriceCalc() = "" And fn_Roadmaps = 1 Then ' Switch separators back
if they were changed to begin with
With Application
.UseSystemSeparators = True
End With
End If
Workbooks(PRP).Saved = True
' Removed Workbooks.Close Savechanges:=False from the end of this clause
End Sub
that cleans up the macro (closes files that it opened, deletes the toolbar
entry, etc), Excel will crash. The spreadsheet is already closed, the Excel
screen is blank, and then "boom".
The thing that is weird is that this only happens in CERTAIN LANGUAGES. US
English doesn't cause an error; but German, Norwegian, and Swedish do
(perhaps others, but those are the ones that are reported in).
To net out what is happening, certain files (partsfile and assume) get
closed if they are the only programs open that use this data, and the
user-created menu bar (not toolbar) gets deleted as well. The funny thing is
that this code worked until recently - I have rebuilt files, I have copied
and pasted into an old version that works, but I am getting anxious users
across Europe. The crash is an Excel error - it is in vbe6.dll. I can
reproduce it when I set my PC to German - but not US English, and I am using
Office 2003 SP3, vbe6 version = 6.5.10.24
I'm running out of ideas - have searched everywhere online, but am stumped.
The program is closed, the files are closed, and then I get the error. Would
greatly appreciate any help on this - the offending routine (from
ThisWorkbook) is pasted below, or so I think. A routine inside the program
did the close, if that matters.
Thanks,
Brandon
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim PartsFile As String
Dim PRP As String
PRP = fn_Roadmap() ' Don't close the wrong file
ActiveWorkbook.SaveLinkValues = False ' Don't save link values to prevent
error message
Call DeleteMenu ' Delete the menu from this PRP
If fn_Roadmaps = 1 Then ' If > 1 then don't close files and delete menus
If fn_PriceCalc() <> "" Then ' Is PriceCalc open? Don't close files
Else
If fn_ISFILEOPEN(Assume) Then
If Workbooks(Assume).Windows.Item(1).Visible = False Then
Workbooks(Assume).Windows.Item(1).Visible = True
If fn_ReadOnlyWB(Assume) Then Workbooks(Assume).Close
Savechanges:=False ' if opened read only close assumptions file
End If
If Worksheets("Data Sheet").Range("D8").Value <> "Canada" Then PartsFile
= "EMParts.xls" Else PartsFile = "CAParts.xls"
If fn_ISFILEOPEN(PartsFile) Then
If Workbooks(PartsFile).Windows.Item(1).Visible = False Then
Workbooks(PartsFile).Windows.Item(1).Visible = True
If fn_ReadOnlyWB(PartsFile) Then Workbooks(PartsFile).Close
Savechanges:=False ' if opened read only close assumptions file
End If
If fn_ISFILEOPEN("EMPartsSales.xls") Then
Workbooks("EMPartsSales.xls").Close Savechanges:=False
If fn_ISFILEOPEN("CAPartsSales.xls") Then
Workbooks("CAPartsSales.xls").Close Savechanges:=False
End If
End If
If fn_PriceCalc() = "" And fn_Roadmaps = 1 Then ' Switch separators back
if they were changed to begin with
With Application
.UseSystemSeparators = True
End With
End If
Workbooks(PRP).Saved = True
' Removed Workbooks.Close Savechanges:=False from the end of this clause
End Sub