CODEWITHSUNDEEP
vbaexcel
Patrick vD
Patrick vD

Reputation: 789

Is there a way to have a piece of VBA code interupt cell calculation?

In an excel project, I have several thousand cells that will display a message box if some user entered data is invalid. Thing is, right now, every time you calculate, you have to close down several hundred message boxes before you can fix the error. Is there a way to have the VBA function that displays the message box also stop the calculation of any other cells?

EDIT: I'm not really looking for some alternate solution to the several hundred message box problem. If I need to, it isn't too hard to fix that. However, I am interested in knowing if there is any way to have a VBA function stop cell calculation, partially because I can see some other uses for it.

Upvotes: 1

Views: 124

Answers (1)

S Meaden
S Meaden

Reputation: 8260

Why raise MessageBoxes at all? Tempted to create your own error messages? Actually returning strings is bad says Chip Pearson here. Use CVErr instead for fast fail.

Or follow the advice of ComIntern by which I think he means write a function that accepts a large block of cells, loop through the cells yourself in code and then break out of loop on first error.

Upvotes: 1

Related Questions