Hi...

I have an auto close routine that closes my excel file.
However, if cell A1 contains the value 1, I want the file
to remain open and an error message appear that
says "please correct errors before closing". This involves
capturing validation within the autoclose code below...how
the hell can I do this?

Sub auto_close()
CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
MenuBars(xlWorksheet).Menus("Data").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Edit").Enabled = True
MenuBars(xlWorksheet).Menus("Format").Enabled = True
MenuBars(xlWorksheet).Menus("Insert").Enabled = True
MenuBars(xlWorksheet).Menus("Window").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Tools").Enabled = True
MenuBars(xlWorksheet).Menus("View").Enabled = True
MsgBox "REMEMBER TO ZIP THIS FILE BEFORE SENDING TO THE
OFFICE"
ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.Quit

End Sub

Gordon,

You can't do it with the Auto_Close procedure. Instead, use the
BeforeClose event procedure in the ThisWorkbook code module. For
example,


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("Sheet1").Range("A1").Value = 1 Then
MsgBox "Please correct errors before closing."
Cancel = True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Gordon C" wrote in message
...
Hi...

I have an auto close routine that closes my excel file.
However, if cell A1 contains the value 1, I want the file
to remain open and an error message appear that
says "please correct errors before closing". This involves
capturing validation within the autoclose code below...how
the hell can I do this?

Sub auto_close()
CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
MenuBars(xlWorksheet).Menus("Data").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Edit").Enabled = True
MenuBars(xlWorksheet).Menus("Format").Enabled = True
MenuBars(xlWorksheet).Menus("Insert").Enabled = True
MenuBars(xlWorksheet).Menus("Window").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Tools").Enabled = True
MenuBars(xlWorksheet).Menus("View").Enabled = True
MsgBox "REMEMBER TO ZIP THIS FILE BEFORE SENDING TO THE
OFFICE"
ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.Quit

End Sub