Conditional Mandatory Entry - If one, all three

**DJBittner** · 11-03-2011, 04:34 PM

On a particular spreadsheet I have 3 columns (let's say columns A, B & C) where if a value is entered in any of A1, B1, or C1, I would like to 'Error Alert' if any of the values in A1, B1, or C1 is left blank/unfilled.

Any thoughts are appreciated.

Thanks, Dan

**TMS** · 11-03-2011, 05:03 PM

You'd have to use a VBA Worksheet Change Event to monitor the three cells. As you can only key into one cell at a time, when do you want to issue a warning?

Regards

**DJBittner** · 11-03-2011, 05:36 PM

Good point -- I would probably want to trigger the alert after the 3rd column is entered or skipped.

With that said, does anyone have an example of a VBA Change Event (trigger?) example for field (multiple field) editing?

**TMS** · 11-03-2011, 05:52 PM

This might work if the cell movement is to the right:

Please Login or Register  to view this content.

However, if you enter two values and cursor down, it wouldn't fire unless you went back to the row after column 3.

Regards

**DJBittner** · 11-03-2011, 07:14 PM

I tried to simplify my example using the first 3 columns when, in fact, my application is using columns 18,19, 20, and 21.

And here is where the plot thickens: everything to the right of column 21 (column U) is protected so the cursor jumps to column 1 (column A) after column 21 (column U) is entered. Manipulating the gifted (and appreciated) code yields either an error msg after the first column is entered, without ability to move to the following (related) fields/columns OR I get no error at all.

**DJBittner** · 11-03-2011, 07:34 PM

In the spirit of trying, I commented out my VBA code and added this to the Data Validation formula line for the cells involved in the range of columns & rows -
=AND(COUNTA($R$9:$R19)=ROW()-2,COUNTA($U$9:$U19)=ROW()-2)
This method wants to work -- however, I get a warning after I enter a value into a column and before I can key into the next column.

So VBA vs. Data Validation?

**Leith Ross** · 11-03-2011, 08:27 PM

Hello DJBittner,

I think this is issue can best be resolved by you posting the workbook for review.

**DJBittner** · 11-04-2011, 11:55 AM

Ok, I have attached my workbook Tables.xlsm. Worksheet TED is my Time-Entry application and the columns I am trying validate are the Materials columns, R, S, T, & U.

As I tried to state previously is that for those 4 columns, if data is entered in any of the columns, then all of the other 3 column values are required.

btw, there is a macro Unprotect, to edit the protected worksheet, "...for just such emergencies." (~foghorn leghorn)

Thanks again, Dan