Msg Help

[mloucel]

Good (Morning/Afternoon) Gurus

I am a newbie and still learning by myself with your help..
I have to check in my worksheet for some cells if they are empty once the user enter his/her name:

date	FName	LName	Phone	Input By
	Peter	Parker		Mary
03/01/2016			(111) 222-3333	John

<tbody>
</tbody>

So when Mary uses the WS she has to Input the first 4 fields then she enters her name
at that point I need to check if there is no data on date, fname, lname, phone all must contain
data, if all or any is empty (or len=0) then simply display a message.
I have to evaluate everytime someone is entering data in every row.
I have the Idea of what I want I just don't know how to do it..

this is my TEST CODE and the place I believe it should be, as you can see I am already checking
some other stuff and even moving all the TXT cells to Uppercase..
I DID try to create the same code as the one in BLUE but of course it was a total failure (I DELETE THE CODE IN FRUSTRATION).


Thank you in advance for your help.. (A NEWBIE)

' Following code corrected 03/08/2016 by MrExcel Forum gurus.
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myRange As Range, otherRange As Range, rngCell As Range


    Application.EnableEvents = False
    Set otherRange = Intersect(Target, Range("G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500"))
    Set myRange = Intersect(Target, Range("AG2:AG1500, AI2:AI1500, AK2:AK1500"))
[B][COLOR=#0000ff]    Set TestInRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500"))[/COLOR][/B]
    
[COLOR=#ff0000][B]    If Len(r2) = 0 Then[/B][/COLOR]
[COLOR=#ff0000][B]        MsgBox " F G H I  are blank"[/B][/COLOR]
[COLOR=#ff0000][B]    End If[/B][/COLOR]
    
[B][COLOR=#0000ff]    If Not otherRange Is Nothing Then[/COLOR][/B]
[B][COLOR=#0000ff]        For Each rngCell In otherRange[/COLOR][/B]
[B][COLOR=#0000ff]            rngCell.Value = UCase(rngCell.Value)[/COLOR][/B]
[B][COLOR=#0000ff]        Next rngCell[/COLOR][/B]
[B][COLOR=#0000ff]    End If[/COLOR][/B]
    
    If Not myRange Is Nothing Then
        For Each rngCell In myRange
            If Len(rngCell) Then
                rngCell.Value = UCase(rngCell.Value)
                If Len(rngCell.Offset(, -1)) = 0 Then rngCell.Offset(, -1).Value = Date
            Else
                rngCell.Offset(, -1).ClearContents
            End If
        Next rngCell
    End If
    Application.EnableEvents = True
    
End Sub

 

[mloucel]

NEVER MIND..

I Figure out my foolish error I FORGOT to declare TestInRange

Then I add my new code:

    If Len("$R2") <> 0 Then
        If TestInRange Is Nothing Then
            MsgBox "There are empty cells..", vbCritical, "Validation ..."
        End If
   End If

and it works..

 

[Joe4]

I think I see another error:

If Len("$R2") <> 0 Then

will always be true, since you are asking if the length of the literal string "$R2" is not zero. The length of that string is 3.

If you are checking for the length of the entry in cell R2, you need to use Range, i.e.

If Len(Range("$R2")) <> 0 Then

 

[mloucel]

I think I see another error:

If Len("$R2") <> 0 Then

will always be true, since you are asking if the length of the literal string "$R2" is not zero. The length of that string is 3.

If you are checking for the length of the entry in cell R2, you need to use Range, i.e.

If Len(Range("$R2")) <> 0 Then

DARN!!!! Again right on the money but hey Thanks to people like you I am slowly but surely learning, please accept my deepest thanks..

 

[Joe4]

You are welcome.

Just keep plugging away at it. You'll get there!

 

[mloucel]

Thank you sir.. and if you don't mind, I thought it will be nice that after my message to force the user to a specific cell:

I know I can use
Call Cells(X,Y).Select
or
Range("$Xn").Select (This would be my favorite)

I would ARGUABLY tempted to find the first EMPTY in my range and go there, but I don't care I rather fix it to $Fn (where $F=Column F ALWAYS, and n=the row I am currently) and let them work..

I know the Columns are always going to be "F though P" But I have no Idea how to find out which Row I am at that point..

If you can give me a tip that would be awesome ...

Thanks again Joe.

This is my Final Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range, otherRange As Range, rngCell As Range, TestInRange As Range


    Application.EnableEvents = False
    Set otherRange = Intersect(Target, Range("G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500"))
    Set myRange = Intersect(Target, Range("AG2:AG1500, AI2:AI1500, AK2:AK1500"))
    Set TestInRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500, I2:I1500, I2:I1500, J2:J1500, K2:K1500, L2:L1500, M2:M1500, N2:N1500, O2:O1500, P2:P1500"))
    
    If Len(Range("$R2")) <> 0 Then
        If TestInRange Is Nothing Then
            MsgBox "There are important empty cells " & vbCrLf & "" _
            & vbCrLf & "YOU MUST Fill these cells:" _
            & vbCrLf & "" _
            & vbCrLf & "Last or First Name,DOB, Chart Number, Phone Number," _
            & vbCrLf & "IP, Plan, Diagnosis, Referred to or Referral by ," _
            & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
            & vbCrLf & " " _
            & vbCrLf & "Please go back and re-enter the values...", _
            vbCritical, "Validation NOT PASSED..."
        End If
   End If
   
   If Not otherRange Is Nothing Then
        For Each rngCell In otherRange
            rngCell.Value = UCase(rngCell.Value)
        Next rngCell
    End If
    
    If Not myRange Is Nothing Then
        For Each rngCell In myRange
            If Len(rngCell) Then
                rngCell.Value = UCase(rngCell.Value)
                If Len(rngCell.Offset(, -1)) = 0 Then rngCell.Offset(, -1).Value = Date
            Else
                rngCell.Offset(, -1).ClearContents
            End If
        Next rngCell
    End If
    Application.EnableEvents = True
    
End Sub

 

[Joe4]

If you want to know which Row you are in when the Event Procedure is invoked, you can use:

Target.Row

So if you wanted to send the user to column F of that particular row, you could use:

Cells(Target.Row,"F").Select

 

[mloucel]

Thanks Joe.. I found an ERROR (my fault) when testing if the len of R2 is 0 was testing only for R2 and I needed to test for R2:R1500 or whatever
so I change my code

 'If Len(Range("R2")) <> 0 Then     'If Not Intersect(Target, Range("R2:R1500")) Is Nothing Then
      If Len(Cells(Target.Row, 18)) <> 0 Then
        If TestInRange Is Nothing Then

to reflect the correct validation, ( I tried 2 diff approaches ) is working and I appreciate your knowledge and Honest answer..

Maurice.