mloucel
New Member
- Joined
- Feb 22, 2016
- Messages
- 37
Good Afternoon Gurus..
I posted this yesterday and was kindly helped by RCBricker (many thanks)
But my problem is somewhat solved thanks to RCBricker, so far I have spent already more than 5 hours trying different things from here and there and I can't find a solution, so I have to ask again for help.
I need to check ONLY certain columns within a row to be empty and display a message to the End user so he can correct the problem
- The EndUser (EU) can be anywhere from row 2 to row 1500.
- When the user enter his/her name in Rn (next available row number in R):
- I need to check colums F thru P, IF ANY of them has nothing then display a message
- some columns as F have a date format (mm/dd/yyyy)
- Phone in K
- and general text the rest
- A thru E are reserved and will never be checked or any data on them.
this is the code I have so far, it works somewhat but not the way I want, partially corrected by RCBricker
PROBLEMS:
- I fill ALL the colums from F to P and enter a name in R and the message pops up
- My code now checks everytime I re-enter something between F and P, like if something is empty when I trully have data all across the cells.
- no matter what even if ALL have data (F thru P) the message pops up every time I enter new data on those colums, so if the EU is entering new data over let's say in F200 after hitting enter I got the message, enter more data in G200 the message comes again, go to fill H and so on.. ANOYING..
I cannot spend more time in this, I am trully at lost and I've done all I can with my little knowledge I have..
Please Gurus can you lend a hand..
I posted this yesterday and was kindly helped by RCBricker (many thanks)
But my problem is somewhat solved thanks to RCBricker, so far I have spent already more than 5 hours trying different things from here and there and I can't find a solution, so I have to ask again for help.
I need to check ONLY certain columns within a row to be empty and display a message to the End user so he can correct the problem
- The EndUser (EU) can be anywhere from row 2 to row 1500.
- When the user enter his/her name in Rn (next available row number in R):
- I need to check colums F thru P, IF ANY of them has nothing then display a message
- some columns as F have a date format (mm/dd/yyyy)
- Phone in K
- and general text the rest
- A thru E are reserved and will never be checked or any data on them.
this is the code I have so far, it works somewhat but not the way I want, partially corrected by RCBricker
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
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"))
Dim rng As Range
Dim lngF As Long, lngCOL As Long
If Len(Cells(Target.Row, "R")) <> 0 Then ' IF the user is in R is R empty or does it have Data ?
Set rng = Range(Cells(2, 6), Cells(1500, 16)) ' Range F2:P1500
[COLOR=#ff0000][B]lngCOL = rng.Find("").Column[/B][/COLOR] ' This line is finding Empty cells even if they have data..
If lngCOL > 0 Then ' IF R has Data then, is there any cell in my TestInRange that is empty ?
MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
& vbCrLf & "YOU MUST Fill these cells:" _
& vbCrLf & "" _
& vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
& vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
& vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
& vbCrLf & " " _
& vbCrLf & "Please go back and re-enter the values...", _
vbCritical, "Validation NOT PASSED..."
' Forcing the User to go to DATE CELL (Fn) within the ROW we are at this moment
ActiveCell.Offset(-1, -12).Select ' Also Generating an error if we are at row 2 but I
End If
End If
End Sub
PROBLEMS:
- I fill ALL the colums from F to P and enter a name in R and the message pops up
- My code now checks everytime I re-enter something between F and P, like if something is empty when I trully have data all across the cells.
- no matter what even if ALL have data (F thru P) the message pops up every time I enter new data on those colums, so if the EU is entering new data over let's say in F200 after hitting enter I got the message, enter more data in G200 the message comes again, go to fill H and so on.. ANOYING..
I cannot spend more time in this, I am trully at lost and I've done all I can with my little knowledge I have..
Please Gurus can you lend a hand..
Last edited: