Preventing closedown unless condition is met. HELP

[Adam Harding]

I am redesigning my budget form to incorporate two sheets, one is a detailed
sheet by nominal code in cost centre for budgets over the next 5 years by
month. The other is a supplier listing with annual budgets against them.

I want to prevent users saving or closing-down the sheet unless the total of
the suppliers budgets = total of the nominal budgets for the year. Simply
unless cell K9 is 0.00 then prevent closing of the form. Please help as this
will save me LOADS of time when it comes to checking these things.

Many thanks in advance

Adam

 

[Earl Kiosterud]

Adam,

This can be done only with programming. You'd use the Before_Close event,
check K9 in your code and set Cancel to TRUE if it don't wash.

Consider this, though. Users will need a way to override this, or can get
into a situation where they can't close the file, and can't balance the
numbers right now. They'll go mad. They'll be like Charlie on the MTA, and
their wives will have to bring them a sandwich every day while they toil
month after month. They'll get religion and pray for a power failure so
they can go home. A smart user will use the Task Manager (Ctrl-Alt-Delete)
and just blow Excel off and go on home cursing you. Better would be a
warning that it's not in balance. A flag can be easily set in a cell, in
red flagrante, indicating out-of-balance, if that would help.

 

[Dave Peterson]

I'd just use a formula in a prominent location--giant letters in Red.

=if(something is ok,"","Please fix this yourdescriptionhere")

And to add to Earl's response. If the user opens the workbook and decides that
they opened the wrong workbook (or just want to close without saving), you'll
have to give them a way to do it.