Hi, some of the code is below. i have far to many lines to be able to to send it all and i cannot share the document due to the nature of it.
If IsEmpty(Range("L12").Value) = True Then
Dim yourFinalMessage As String
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 12 - Please enter the ID of the person completing this form"
End If
If IsEmpty(Range("L20").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 20 - Please enter the Number for this request"
End If
If IsEmpty(Range("L28").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 28 - Please enter the contact details for this request"
End If
Hope that can help you help me as the bit above doesn't really make much sense to me. also i didn't include the final end string line but the code above does work
Please use code tags when posting code. It makes it easier to read and saves space on the page.
code tags
open square bracket "[" the word "code" close square bracket "]"
so (without the spaces)
[ code ]
then
open square bracket "[" the word "/code" close square bracket "]"
so (without the spaces)
[ /code ]
paste code between those two lines (ends up looking like the following)
Code:
If IsEmpty(Range("L12").Value) = True Then
Dim yourFinalMessage As String
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 12 - Please enter the ID of the person completing this form"
end If
If IsEmpty(Range("L20").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 20 - Please enter the Number for this request"
End If
If IsEmpty(Range("L28").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 28 - Please enter the contact details for this request"
End If
Also only dim at the start of the procedure. Makes it easier to find them all and just looks cleaner.
since you are looking at each instances in the L column for blanks, it might be easier if you used a loop and/or a select case. This will shorten your code (make it easier to debug and read) and make it faster. I understand that you can not share the document, but perhaps some questions.
Your first IF statement check cell L12 and obviously is for the ID of the person completing the form. Is there a row designator that states ID. So how would someone unfamiliar with your spreadsheet know that row 12 is for Person's ID, row 20 is for the number, row 28 is for contact details.
Are all those designators in a specific column? the same column?
If so you can do a loop would take a the row number and then a select case that checks the row number and determines the msgbox.
so if all your data starts in row 2 of column L then you could do something like the following
Code:
Sub PGD15()
Dim lngROW As Long, lngCELL As Long
Dim rng As Range, cell As Range
Dim ws As Worksheet
Dim yourFinalMessage As String
Dim varI As Variant
Set ws = ActiveSheet
Set yourfinalmeassage = "my message"
With ws
lngROW = ws.Range("L" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("L2:L" & lngROW)
varI = 0
For Each cell In rng
If isemty(cell.Value) = True Then
lngCELL = cell.Row
varI = varI + 1
Select Case lngCELL
Case 12
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 12 - Please enter the ID of the person completing " _
& "this form"
Case 20
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 20 - Please enter the Number for this request"
Case 28
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 28 - Please enter the contact details for this request"
End Select
End If
Next cell
If varI = 0 Then
MsgBox = "No Issues"
Else
MsgBox = yourFinalMessage
End If
End With
End Sub