MACRO to check validation dropdowns left blank

Need your help guys,

I'm new at using macros - just learned a month ago.

Any suggestions on this one:

Got a table where I've placed Data validation - List on them.

Placed a macro on a button so that the data on the table where the validation list is placed is transfered to another sheet (same workbook).

What I need is a to modify the macro assigned to the button so that when the button is clicked to activate the macro:
a.) Can identify if there are any blanks on specified cells with validation list
b.) Stops the macro from copying the fields to the other sheet
c.) Alert the user of the blanks (an alert stating there is a blank would suffice)
d.) Alert should preferably be similar to a Stop alert from Data Validation.

Can anyone help? Been reading through several threads but don't seem to find anything that could help me - tried reading a Visual basic thread and now i've got a headache....

Whew...

Thanks

Thanks

Will try this tomorrow. After a few advils....

Tried to use this but code won't work - Came back with error. Syntax Error.
The first line got higlighted in yellow and the 2nd line got font color red.

Any one please?

Hi,

Thanks roy for pointing it out, I just realised that we can't use Resume label on same statement as On Error. But if we use GoTo label instead, the error condition will remain. Will it affect the subsequent statements if they ever get an error?

By the way, this statement will select the entire spreadsheet, then filter to those with data validation of any type, then filter to blanks.

Please Login or Register  to view this content.

The better statement is to refer to a single cell, then select the range with same data validation and then blanks. For e.g.

Please Login or Register  to view this content.

Thanks a lot guys.

I'll give a try tomorrow

Hi again,

Tried the new code - this time no syntax error. Good.
However when I run the code, even if the range or cell with validation is no longer blank the Msgbox indicating there is a blank still pops out and the copying does not proceed.

I'd like to place the code here plus the copying code I recorded but don't know how to wrap the code.

Thanks

This is the code

Please Login or Register  to view this content.

RoyUK - Thanks for the link for wrapping codes

When there is a blank the code works exactly the way Xiao outlined it above and I quote " However, this will check only one at a time. So if there are blanks in D6:E6 and D20 as well, the message box will prompt with first message (i.e. CA Name cannot be blank) only."

However when all the specified ranges are not blank an error apppears

400


I see that the ranges set to be copied has been copied (highlighted). Can we assume the action or code stopped there then has an error.

Note: I tried using both codes: first by Roy's then Xiao's but has the same result.

Modified the code a bit to match the ranges. And changed the copying part of the code to simplify.

Please Login or Register  to view this content.

Attached is the workbook

Thanks

Thanks Roy.

Work perfectly ! ! !

Just had to move the 'Exit Sub' before the 'isBlank:'

Thanks so much.


How do I tag this thread as solved?