Using Data Validation with VBA

[patele]

I'm have custom data validation in a variety of cells forcing a user to make an entry in prior cells before continuing on to the next cell to ensure each cell has an entry in it. I also need to have validation in some of those same cells to limit what data can be entered into the cell, i.e. YES, NO or Sm, Med, Lg, XL etc. etc. Is there a vba code that would work in conjuction with data validation in a cell to either ensure all the required cells have an entry or limit what can be entered into the cell. Basically in a nut shell I need to ensure a cell has an entry and limit what can be entered into the cell. Data validation appears to let me do one or the other. Any ideas or suggestions is greatly appreciated. Thanks
Ed

 

[TFCJamieFay]

You could use something like:

If Range("A1").Value = "" Then
Msg "Please insert an answer"
Range("A1").select
Exit sub
End if

for each cell you wish to vaidate assuming A1 is the one you wish to check.

HTH Jay

 

[DonkeyOte]

You can almost (might be able) to do this using just validation...

assume that your three cells were A1,B1,C1 and we're looking at B1
assume your list of valid entries for B1 is listed in say C2:C4

you could use something like this in B1

=AND(COUNTA($A$1:$A1)=(ROW($B2)-1),MATCH($A2,$C$2:$C$4,0))

this would do a double evaluation - ie ensure count of non blanks up to prior row = expected number and that current value can be found in validation list

The counta part would work for additional cells but assuming each cell has different valid entries you may need to be creative regards the MATCH part to look in a dynamic range rather than always in C2:C4 for ex.

However, with validation there's nothing to stop someone going back and simply deleting the cell value say in A1 at a later point...you can't stop the clear without VBA.