Msg Help

mloucel

New Member
Joined
Feb 22, 2016
Messages
37
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:

dateFNameLNamePhoneInput By
PeterParkerMary
03/01/2016(111) 222-3333John

<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)

Code:
' 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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mloucel

New Member
Joined
Feb 22, 2016
Messages
37
NEVER MIND..

I Figure out my foolish error I FORGOT to declare TestInRange

Then I add my new code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows
I think I see another error:
Code:
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.
Code:
If Len(Range("$R2")) <> 0 Then
 
Last edited:

mloucel

New Member
Joined
Feb 22, 2016
Messages
37
I think I see another error:
Code:
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.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.

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

mloucel

New Member
Joined
Feb 22, 2016
Messages
37
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.. :LOL:

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:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows
If you want to know which Row you are in when the Event Procedure is invoked, you can use:
Code:
Target.Row

So if you wanted to send the user to column F of that particular row, you could use:
Code:
Cells(Target.Row,"F").Select
 

mloucel

New Member
Joined
Feb 22, 2016
Messages
37
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


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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top