disable close if conditions are not met

[Michelle K]

hi,

i am writing a worksheet in excel. i need to disable the close button on
both the window and the menu bar if a certain total has not been met (i.e. if
they haven't answered the minimun number of questions they shouldn't be able
to close the file)

how do i do this? if you think this can be done more efficiently in any
other office application let me know as well.

thanks,
michelle k

[Jim Thomlinson]

The problem with doing this is if they can't get the correct number then they
can't close the sheet. Users will start rebooting like mad if they can't get
it closed. A better option is to in thisworkbook at the on_close event show a
warning that the sheet is not properly filled out and cancel the close
event... Something like this...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Citeria not met in an if statement
If MsgBox("Not completed correctly...Would you like to try again?",
vbCritical + vbYesNo, "Error") = vbYes Then
Cancel = True
End If
End Sub

HTH

"Michelle K" wrote:

> hi,
>
> i am writing a worksheet in excel. i need to disable the close button on
> both the window and the menu bar if a certain total has not been met (i.e. if
> they haven't answered the minimun number of questions they shouldn't be able
> to close the file)
>
> how do i do this? if you think this can be done more efficiently in any
> other office application let me know as well.
>
> thanks,
> michelle k

[Ron de Bruin]

Try this one

Copy this event in the Thisworkbook module
Change the sheet name and ranges in this line
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "fill in all cells"
Cancel = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Michelle K" <MichelleK@discussions.microsoft.com> wrote in message news:F640BF01-85BE-4182-8C99-621A66F89C1F@microsoft.com...
> hi,
>
> i am writing a worksheet in excel. i need to disable the close button on
> both the window and the menu bar if a certain total has not been met (i.e. if
> they haven't answered the minimun number of questions they shouldn't be able
> to close the file)
>
> how do i do this? if you think this can be done more efficiently in any
> other office application let me know as well.
>
> thanks,
> michelle k

[K Dales]

The easiest thing to do (rather than disabling the close menu/button) is to
catch the problem when the use tries to close the workbook. You do this with
the Workbook_BeforeClose event procedure. That is, go into VBA,
double-click"ThisWorkbook" in the Project Explorer, and then type something
like this (the first line, declaring the Sub, must be copied exactly, as is
the part that says Cancel=True):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Range("A1") < 1000 Then
MsgBox "The total in A1 must be at least 1000!" _
& " Please complete worksheet before closing.", vbExclamation, "CANNOT
CLOSE:"
Cancel = True
End If

End Sub




"Michelle K" wrote:

> hi,
>
> i am writing a worksheet in excel. i need to disable the close button on
> both the window and the menu bar if a certain total has not been met (i.e. if
> they haven't answered the minimun number of questions they shouldn't be able
> to close the file)
>
> how do i do this? if you think this can be done more efficiently in any
> other office application let me know as well.
>
> thanks,
> michelle k

[Michelle K]

thank you guys. they all sound great. i'll try them all out right now.

"Michelle K" wrote:

> hi,
>
> i am writing a worksheet in excel. i need to disable the close button on
> both the window and the menu bar if a certain total has not been met (i.e. if
> they haven't answered the minimun number of questions they shouldn't be able
> to close the file)
>
> how do i do this? if you think this can be done more efficiently in any
> other office application let me know as well.
>
> thanks,
> michelle k