Validation Button

[twofootgiant]

Hello

I have a sheet with multiple columns...some of which MUST end up with data in them.

When the user has filled in the sheet he will email it to me. Before he emails it to me I would like a button which, when clicked flags up any cells which should have been filled in but havnt. It doesnt matter how it flags them as long as the user will be presented with them.

the sheet is called 'enter data' and there are 15 column which need checking for empty cells..6 - 2506 cells deep.

Would it be easier if I named these ranges?

Any help would be much appreciated as I am well and truly STUMPED.

[Richard Buttrey]

Hi,

I'd be inclined to use a combination of conditional formatting to highlight the cells, and then a control formula at the top of each column containing a COUNTA(range) formula which evaluates the number of non blank cells in the range, along with a constant value for each column which expresses the number of cells that must be completed.

So for instance a conditional format in A6 using the Formula is option would be =A6="", this should then be copied to each of the cells in the range you're checking.

If there are lots of gaps in the 2500 cells per column which you're not interested in, then yes, use named ranges. So for instance if A1 contains the constant for the number of cells you expect to be completed, then A2 should be

=IF(COUNTA(myrange1,myrange2,myrange3.....etc)<>A1,1,0)

Then I'd have another cell on row 2 which sums the 1s or 0s e.g.

P2: =SUM(A2:O2)

and make this cell total, which you need to be zero, the subject of a message box which pops up to warn the user that there is missing data when s/he tries to email the sheet.

You could be even more sophisticated with this if you wanted, and work out an algorithm to say which column is missing data, although it's probably sufficient to expect the user to eyeball the columns looking for the conditional format highlights.

HTH

[twofootgiant]

Hi Richard, Thanks for the reply

is there not some kind of macro code which I could use?

When the user hits the validation button I would like a message box to appear saying how many cells in total are still needing to be filled in. I will use a user form for the message box as I need two options from there, either 'cancel' or 'show me'. The cancel is pretty obvious.

But the 'show me' button: I would like this to take the user straight to the cell which has been left empty ( and be vbmodeless so he can fill it in with the userform still open).

When he clicks 'show me' it will 1st do the above...but also another button will appear on the form: 'next'.

Next button: after the user has clicked 'show me' and filled in the cell which needs filling he will click next...this will take him to the next cell.

So thats what I want it to do.
This seems like its gna b dead complicated....anyone up 4 a challenge?

[twofootgiant]

really desperate for some help! I have to get this done for work and my deadline is getting very close.