Hi,

I've got a couple of files with VBA code, nothing too complicated.
Oftentimes, when I open one of these files and use them, if I try to close
them, answering "no" to saving changes, I get an error message from Excel
that it is closing down, and do I want to receive an error report. I
received an error report once, but of course, nothing made any sense to me.

The files on which this happens work fine otherwise. I've narrowed the
problem down to when I close the files without saving changes. Sometimes
(very rarely) it will return an error message if I try to save the changes,
but more often it's when I answer "no."

This happened with my older version of Excel (XP) and I still have the
problem using 2003.

Any suggestions?

Thanks in advance,
Jason

Well you'll need to post your code for us to help you out

"jcliquidtension" wrote:

Hi,

I've got a couple of files with VBA code, nothing too complicated.
Oftentimes, when I open one of these files and use them, if I try to close
them, answering "no" to saving changes, I get an error message from Excel
that it is closing down, and do I want to receive an error report. I
received an error report once, but of course, nothing made any sense to me.

The files on which this happens work fine otherwise. I've narrowed the
problem down to when I close the files without saving changes. Sometimes
(very rarely) it will return an error message if I try to save the changes,
but more often it's when I answer "no."

This happened with my older version of Excel (XP) and I still have the
problem using 2003.

Any suggestions?

Thanks in advance,
Jason

Hi James:

Although the code isn't too complicated, there are quite a few functions.
I've included the code from my personal workbook first, since I have this
problem with more than one file while attempting to close. I also included
the code in one of the files that gives me an error when trying to close
(most of the time when not saving changes).

The following code is in my personal workbook (module 1) which I use
frequently on most files:
Sub Today()
'
' Today Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
Sub Highlight()
'
' Highlight Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+H
'
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
Sub Unhighlight()
'
' Unhighlight Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Selection.Interior.ColorIndex = xlNone
End Sub
Sub Protect()
'
' Protect Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Sub Unprotect()
'
' Unprotect Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+F
'
ActiveSheet.Unprotect
End Sub
Sub PasteValue()
'
' PasteValue Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
Sub PasteFormat()
'
' PasteFormat Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Sub PasteFormula()
'
' PasteFormula Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Sub Center()
'
' Center Macro
' Macro recorded 9/23/2005 by SFM Inc
'
' Keyboard Shortcut: Ctrl+Shift+C
'
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

Sub CopyAbove()
'
' CopyAbove Macro
' Macro recorded 10/6/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

The next code is from one workbook with which I have problems when I try to
close:

- in Sheet 1 (Input) the command button is on sheet 1
Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

- in the code for a command button on userform1:
Private Sub CommandButton1_Click()

UserForm1.Hide

End Sub

- in Module 1:
Sub Wed()
'
' Wed Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("F10").Select
End Sub
Sub Thur()
'
' Thur Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=21
Range("F31").Select
End Sub
Sub Fri()
'
' Fri Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=42
Range("F52").Select
End Sub
Sub Sat()
'
' Sat Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=63
Range("F73").Select
End Sub
Sub Sun()
'
' Sun Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=84
Range("F94").Select
End Sub

- in Module 2:

Sub Mon()
'
' Mon Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=105
Range("F115").Select
End Sub
Sub Tues()
'
' Tues Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Range("A9").Select
ActiveWindow.SmallScroll Down:=126
Range("F136").Select
End Sub
Sub Timesheet()
'
' Timesheet Macro
' Macro recorded 5/27/2005 by Jason Cogle
'

'
Sheets("Timesheet").Select
End Sub

- and lastly, in Module 3:
Sub PrintPreview()
'
' PrintPreview Macro
' Macro recorded 6/8/2005 by Jason Cogle
'

ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="1"
ActiveWindow.SmallScroll Down:=39
ActiveWindow.SelectedSheets.PrintPreview
Selection.AutoFilter Field:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

"James W." wrote:

Well you'll need to post your code for us to help you out

"jcliquidtension" wrote:

Hi,

I've got a couple of files with VBA code, nothing too complicated.
Oftentimes, when I open one of these files and use them, if I try to close
them, answering "no" to saving changes, I get an error message from Excel
that it is closing down, and do I want to receive an error report. I
received an error report once, but of course, nothing made any sense to me.

The files on which this happens work fine otherwise. I've narrowed the
problem down to when I close the files without saving changes. Sometimes
(very rarely) it will return an error message if I try to save the changes,
but more often it's when I answer "no."

This happened with my older version of Excel (XP) and I still have the
problem using 2003.

Any suggestions?

Thanks in advance,
Jason

Hi Jason

I don't have a solution but a couple of observations.

Firstly:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

This is hiding userform1 and not closing it so it may be that this
userform is still loaded when you try to close the file which could
cause problems. Try
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

Secondly:
Sub Today()
'
' Today Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

This macro has the same name as an inbuilt excel function which is not a
good idea. It is also not needed as it replicates the inbuilt excel
shortcut Ctrl+;.

Hope this helps
Rowan

jcliquidtension wrote:
Hi James:

Although the code isn't too complicated, there are quite a few functions.
I've included the code from my personal workbook first, since I have this
problem with more than one file while attempting to close. I also included
the code in one of the files that gives me an error when trying to close
(most of the time when not saving changes).

The following code is in my personal workbook (module 1) which I use
frequently on
<snip

Thanks much Rowan! I wasn't sure anyone would have the patience to read the
book that I posted. I really appreciate the help/tips!


"Rowan Drummond" wrote:

Hi Jason

I don't have a solution but a couple of observations.

Firstly:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

This is hiding userform1 and not closing it so it may be that this
userform is still loaded when you try to close the file which could
cause problems. Try
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

Secondly:
Sub Today()
'
' Today Macro
' Macro recorded 9/21/2005 by Jason Cogle
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

This macro has the same name as an inbuilt excel function which is not a
good idea. It is also not needed as it replicates the inbuilt excel
shortcut Ctrl+;.

Hope this helps
Rowan

jcliquidtension wrote:
Hi James:

Although the code isn't too complicated, there are quite a few functions.
I've included the code from my personal workbook first, since I have this
problem with more than one file while attempting to close. I also included
the code in one of the files that gives me an error when trying to close
(most of the time when not saving changes).

The following code is in my personal workbook (module 1) which I use
frequently on
<snip