Stop Workbook from closing.

[Guest]

I have been playing around with running a macro before a sheet is closed, and
I have found it very useful.
I don't know if this is possible of not, so I would apreciate your advice,

when a user attempts to close the workbook, I want my macro to run a check,
and if a specific cell has no value in it, I want a popup to say "please
enter vallue in cell..." When the user clicks ok, instead of exiting the
workbook, I would like it to close the popup and keep the workbook open for
the user to add a value.

I can do all of this exept the part where I want it to keep the workbook open.
Of course as soon as you click ok the workbook closes.

Is there any way to do this, or am I just hitting my head against the
wall....?

Any help is greatly appreciated.

Melissa.

 

[Norman Jones]

Hi Melissa,

Try:

'=============>>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range

Set rng = Me.Sheets("Sheet1").Range("A1") '<<==== CHANGE

If IsEmpty(rng) Then
MsgBox "An entry is requires in " _
& rng.Address(0, 0, , 1)
Cancel = True
End If

End Sub
'<<=============

 

[Guest]

thankyou soooo much. After all the stuffing around that I did, I can't
believe it is so simple!

To take this one step further, I would like to do the same thing if one cell
in a particular range is empty.

ie range a1:a100
if there a values in cells 1 to 50 the let it close,
if there a values in cells 1 to 90 then let it close,
but if there is a blank gap somewhere in these cells with values, then bring
up the poppup.

My mind is racing and you have helped me out tramendously, and I know I can
figure the rest out . so I don't mind if you want to leave me to learn the
rest for myself,

but If it is simple enough for you, it would save me alot of time,

thanks again for your help

melissa

 

[Jim May]

If Range("B2:B90").SpecialCells(xlCellTypeBlanks).Select = True Then
MsgBox "You have blank cells in your range"
End If

 

[Norman Jones]

Hi Melissa,

Try:

'=============>>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range

Set rng = Me.Sheets("Sheet1").Range("A1:A100") '<<==== CHANGE

If Application.CountA(rng) < rng.Cells.Count Then
MsgBox "An entry is required in ALL cells in " _
& rng.Address(0, 0, , 1)
Cancel = True
End If

End Sub
'<<=============

 

[Guest]

thankyou for all your help, I guess it was lazy of me to ask you to do the
rest for me,

I have figured it out now, but I would' have been able to do it without your
help

melissa