Macro to check the empty Cells in a row before closing the file

Hi everyone :-)

I have a tricky question - do you know, if there is a possibility to have macro which will check my cells in one specific column before file is closed? Could anyone help me to update macro as follows:

- to see in message box in which row is empty cell
- to show message before file is closed when "Close File" button is pressed? So when I close the excel file message will pop up?

My macro is:

Sub IsCellBlank()
If IsEmpty(Range("AD")) Then
MsgBox "Empty cell in row 125. Please correct and chose the right root cause"
End If
End Sub


Thank you lot

Andy :-)

Hi, Andy,

please wrap code-tags around the procedure you show in the opening post - thank you.

Maybe like this:

Please Login or Register  to view this content.

You should alter the name of the sheet to check. Code could either be called in the Workbook_BeforeClose/BeforeSave-event (first should be cancelled if an empty cell is shown) or in the Worksheet_Deactivate-event.

Ciao,
Holger

suppose the file is a saved file. open the file
open vb editor (alt F11)
click control+R
all the open files will be listed.
choose this file unde this file right click "thisworkbook" and click view code
in the widow that comes up copy paste this EVENT CODe and save the file
now when you close the file (click x at top right) pop up message will come and act on that.

Please Login or Register  to view this content.

Hi, venkat1926,

two things: I doubt it to be a good idea to let the user close a file and tell the person to open it agiain (you´re always following everything what you have been told?). That´s what the Cancel = True should take care of.

And I pretty much expect the message for blanks to appear on every closing of the file as you search the entire column instead of just the range with data. You can overcome that in filling out each cell in Column AD beneath the used range.

Ciao,
Holger