Greetings,

I am looking for a macro to accomplish following:

I have a requirement where I need to check following before closing a
worksheet:

1) If any cell (from say line 1 to 10) in column A is empty (null),
then user should be shown a message box asking him to fill in that
cell before closing excel.
2) If the step 1 is ok, then we should also check to make sure that
the immediate next two column's value (say Columnn B and Column C),
should also be filled in.

So, in a nutshell,

Step 1"

ColA

Cell1 (Value1)
Cell2 Null
Cell3 (Value2)

In the above scenario, user should be given message box, when he tries
to close excel, asking him to fill in.

Also, once the above step is through,

Step 2:

ColA ColB ColC

Cell1 (Value1) Value4 Value6
Cell2 (value2) Null Value7
Cell3 (Value3) Value 5 Null

The user again should be promted with a message box, when he tries to
close the excel, asking him to fill the two values in ColB and ColC

Any advise will be appreciated

TIA

Hi,

Try this before_close code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").Range("A1:C10")
myvals = WorksheetFunction.CountA(MyRange)
If myvals < 30 Then
MsgBox "You must fill in all the cells on sheet1"
Cancel = True
End If
End Sub

Mike

"Pankaj" wrote:

Greetings,

I am looking for a macro to accomplish following:

I have a requirement where I need to check following before closing a
worksheet:

1) If any cell (from say line 1 to 10) in column A is empty (null),
then user should be shown a message box asking him to fill in that
cell before closing excel.
2) If the step 1 is ok, then we should also check to make sure that
the immediate next two column's value (say Columnn B and Column C),
should also be filled in.

So, in a nutshell,

Step 1"

ColA

Cell1 (Value1)
Cell2 Null
Cell3 (Value2)

In the above scenario, user should be given message box, when he tries
to close excel, asking him to fill in.

Also, once the above step is through,

Step 2:

ColA ColB ColC

Cell1 (Value1) Value4 Value6
Cell2 (value2) Null Value7
Cell3 (Value3) Value 5 Null

The user again should be promted with a message box, when he tries to
close the excel, asking him to fill the two values in ColB and ColC

Any advise will be appreciated

TIA

On Sep 4, 4:04*pm, Mike H wrote:
Hi,

Try this before_close code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").Range("A1:C10")
myvals = WorksheetFunction.CountA(MyRange)
If myvals < 30 Then
MsgBox "You must fill in all the cells on sheet1"
Cancel = True
End If
End Sub

Mike



"Pankaj" wrote:
Greetings,

I am looking for a macro to accomplish following:

I have a requirement where I need to check following before closing a
worksheet:

1) If any cell (from say line 1 to 10) in column A is empty (null),
then user should be shown a message box asking him to fill in that
cell before closing excel.
2) If the step 1 is ok, then we should also check to make sure that
the immediate next two column's value (say Columnn B and Column C),
should also be filled in.

So, in a nutshell,

Step 1"

ColA

Cell1 (Value1)
Cell2 *Null
Cell3 (Value2)

In the above scenario, user should be given message box, when he tries
to close excel, asking him to fill in.

Also, once the above step is through,

Step 2:

ColA * * * * * * * ColB * *ColC

Cell1 (Value1) * * Value4 * * *Value6
Cell2 (value2) * * Null * *Value7
Cell3 (Value3) * * Value 5 * * Null

The user again should be promted with a message box, when he tries to
close the excel, asking him to fill the two values in ColB and ColC

Any advise will be appreciated

TIA- Hide quoted text -

- Show quoted text -

Thanks Mike. Can we modify this code in order to make sure that the
values for Col B and ColC are only checked when the value in ColA is
filled in. What I mean is we need to prompt for a message to user only
when

(ColA.cell1 < NULL) AND (ColB.cell1 = NULL or ColC.cell1 = NULL).

Appreciate you quick response.

TIA