Excel Columns Validaton

1. =OR(LEN(A1)=0,AND(LEN(B1)>0,AND(LEN(C1)>0,LEN(D1) > 0)))

2. =OR(N1<>"Yes",LEN(O1)>0)

3. =OR(LEN(N1)>0,LEN(O1)=0)

Is this what you had in mind.

The code prompts the user when invalid entries are made.

I have attached a test file.

Michael

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Range.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Range.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Range.Row
      End If
   End If
End Sub

Select allOpen in new window

test.xls

Each above formula returns true if the validation is met, false otherwise.

1. If A has no value then it is always true, but if there is a value in A then B, C, and D must also have values or it will return false.

2. If N is not "Yes", it will always be true, otherwise, O must contain a value for it to be true.

3. If N is not blank, it will always be true, otherwise, O must be blank for it to be true.

Hi guys,

Either one of those maybe a solution but I am not a vb or excel experts so here's how I went about testing them:

1. MajorBigDeal - I click the "fx" which I believe the function, then I copied & pasted your first line.
Then, I deleted values in column B where column A still have values, I was able to save & close the spreadsheet which is not right. I should have been prompted with an error message that B should also have a value.
I am not sure what I am doing wrong, but are you are able to give me detailed step on how to implement/test this.



2. Michael74 - Your code is also great, however, didn't work for me.
How I tested this:
1. I opened the Excel worksheet
2. Went to Tools - Macro - Visual Basic Editor
3. Copied and paste your code above there
4. Save & closed the worksheet
5. Went back in and tried to remove data from column B where A is not blank but I was still able to save the worksheet without errors.

If this has worked, then I should've been prompted with an error message. Is this correct?

Thanks again guys

Hi varvoura,

You'll need two different codes in order to work as you need.

1) To clear values in column O when there is an empty value in Column N you should use this code in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ran As Range
Set ran = Range("N:O")
If Intersect(Target, ran) Is Nothing Then Exit Sub
If Range("N" & Target.Row) = "" And Range("O" & Target.Row) <> "" Then
Range("O" & Target.Row) = ""
End If
End Sub


2) To avoid the saving and prompt the user when the conditions you said are not met, then this code must go in the Thisworkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim rng As Range, cell As Range, rng2 As Range, lrow As Long
 
 With Sheets("Hoja1")
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<-- Finding last row basis of F Column
 
    Set rng = Range("B1:D" & lrow)
    Set rng2 = Range("N1:N" & lrow)
   
    For Each cell In rng
        If Range("A" & cell.Row).Value <> "" And cell.Value = "" Then
             MsgBox "Cell " & cell.Address & " is Empty... Please type a value"
   SaveAsUI = False
Cancel = True
        End If
        Next
   For Each cell In rng2
        If Range("N" & cell.Row).Value = "YES" And Range("O" & cell.Row).Value = "" Then
        MsgBox "Cell " & cell.Address & " Need an asociate value in column O "
   SaveAsUI = False
Cancel = True
   End If
   Next
   End With
End Sub

This will avoid saving unless your conditions are met.

Hope it works for you.
Roberto.
P.S. See attached file.
Libro1.xls

In order to create a msgbox, you will need to use a VBA approach that is being proposed by others.  But when would you want this to happen?  Is the user going to press a button in order to run the validation?  Are you really going to try to stop them from saving and closing the spreadsheet?  What if they have entered a bunch of data but it is not yet correct?

I use formulas like the ones I showed you to validate code, for example, by using conditional formatting, cells that do meet the requirements can be highlighted in different colors.  Or a message can be displayed in a cell. It is simple and doesn't require VBA.  BTW, I am good at VBA and writing "bullet-proof" VBA applications in excel requires a lot of code similar to what is being offered by others.  It is very labor-intensive.  You see how much code is being generated just to do a few simple validations.

I think we might want to take a step backwards and discuss the application that you are actually trying to create.  Are you trying to collect data from a large number of users and then consolidate it? Or is this a "single-user-at-a-time" application? Will the data in a spreadsheet have many similar rows, or is it essentially a single form  with each field only being used once?   Depending on what you are trying to do, it may be that excel is only part of the solution that you are looking for.

Hi guys, you all are amazing, great code all over. I am working with some of your examples at the moment.

MajorBigDeal - Single user & used for data compilation & import - awsome code, don't know where to include it to make it useful.

Michael74 - awsome code, however, error on compilation even with your test file.

Pabilio - awsome code as well & I am working through some of it as we speak

One more question for everyone though:

Is there a way using VBA to highlight the errored cell in yellow. This is a lot simpler to the user to identify the cells which have the problem as the cell.Address is not too informational for novice users.

If highlighting is not possible, how can I then get the cell label rather than the address. For example, if $B$2 is blank whenever it is not suppose to, how can I display B2 in the error message box instead of $B$2.

thanks

Hi again,

1. Can I make an entire column changed to upper case in VBA?

For example, where the user enters "y" to change it to "Y"

2. Can I force the user to enter a certain value in a column. For example, in some of the columns I want the users to enter "Yes" or "No". but I don't want the user to accidently enter "Y" or yes or "N" for No.

sorry I have corrected the fault in my example. will have a look at your other requests tomorrrow.

To force a user to select an option use data validation. Sheet 1 column B has been setup with this.
http://support.microsoft.com/kb/211485

Note if copying the code into your workbook it needs to go into the sheet which validation is to be done eg in my example it in sheet 1

Michael

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Target.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Target.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Target.Row
      End If
   End If
End Sub

Select allOpen in new window

test.xls

Hi Michael/everyone,

For sheet 1 in test.xls column B is exactly what I need, however, i need it to start on row 2 as my data starts on row 2 and row 1 is only for column titles. The last thing i want is a drop down to appear on column title for users to select from because they will try.

Any suggestions everyone?

Thanks

Just remove the validation on the first cell of column B.

I have updated the test file to ignore row 1 and remove the validation on these cells

Michael

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing And Target.Row <> 1 Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Target.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing And Target.Row <> 1 Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Target.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Target.Row
      End If
   End If
End Sub

Select allOpen in new window

test.xls

Thanks very much Michael.

I have already worked this out today and implement it at work.

Hi Micheal,

the new text.xls is not compiling, there's an error.

Here is the file again

Michael
test.xls