Stop Workbook from closing.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

beauty_bobaloo - 23 Feb 2006 11:09 GMT

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.

Reply to this Message

Norman Jones - 23 Feb 2006 11:34 GMT

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
'<<=============

---
Regards,
Norman

>I have been playing around with running a macro before a sheet is closed,
>and
[quoted text clipped - 19 lines]
>
> Melissa.

Reply to this Message

beauty_bobaloo - 23 Feb 2006 12:00 GMT

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

> Hi Melissa,
>
[quoted text clipped - 42 lines]
> >
> > Melissa.

Reply to this Message

Jim May - 23 Feb 2006 12:44 GMT

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

> thankyou soooo much. After all the stuffing around that I did, I can't
> believe it is so simple!
[quoted text clipped - 72 lines]
>> >
>> > Melissa.

Reply to this Message

Norman Jones - 23 Feb 2006 12:45 GMT

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
'<<=============

---
Regards,
Norman

> thankyou soooo much. After all the stuffing around that I did, I can't
> believe it is so simple!
[quoted text clipped - 20 lines]
>
> melissa

Reply to this Message

beauty_bobaloo - 23 Feb 2006 12:46 GMT

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

> thankyou soooo much. After all the stuffing around that I did, I can't
> believe it is so simple!
[quoted text clipped - 64 lines]
> > >
> > > Melissa.

Reply to this Message