Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hold closing the sheet...
Hi I have to validate the data at the time of closing the sheet. If some errors are present in the data, I need to throw a message and I should not close the sheet. I am using the following code to achieve it. Here the problem is I can throw an error message if the data is wrong but how to stop the work book from closing. I have to give a chanse to user to correct the data. How this can be achieved? Private Sub Workbook_BeforeClose(Cancel As Boolean) { Validate the data } If Error Msgbox ( " Error .... Correct the Data " ) End Sub -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hold closing the sheet...
I would use the BeforeSave and BeforeClose events in
conjunction with each other. Here's an example: '//Constructive criticism from VBA programmers appreciated Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _ Boolean, Cancel As Boolean) Dim checkRng As Range Set checkRng = Sheets("Sheet1").Range("A1") If IsError(checkRng) Then Cancel = True MsgBox "Error found in " & _ checkRng.Address(False, False) & "." End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If Me.Saved = False Then MsgBox "Please save before closing." Cancel = True End If End Sub -- Both macros should be placed in the ThisWorbook module. HTH Jason Atlanta, GA -----Original Message----- Hi I have to validate the data at the time of closing the sheet. If some errors are present in the data, I need to throw a message and I should not close the sheet. I am using the following code to achieve it. Here the problem is I can throw an error message if the data is wrong but how to stop the work book from closing. I have to give a chanse to user to correct the data. How this can be achieved? Private Sub Workbook_BeforeClose(Cancel As Boolean) { Validate the data } If Error Msgbox ( " Error .... Correct the Data " ) End Sub -- Message posted via http://www.officekb.com . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i open another sheet in the same workbook without closing? | Excel Discussion (Misc queries) | |||
Warning upon closing sheet | Excel Worksheet Functions | |||
How to find and Replace hyperlink in hold sheet? | Excel Discussion (Misc queries) | |||
How can I hold every other row color on a sheet when I sort | Excel Discussion (Misc queries) | |||
set time limit before closing sheet | Excel Programming |