Validation

  • Thread starter Thread starter mark_hall
  • Start date Start date
M

mark_hall

Hi,

I currently have this macro to check whether certain cells are
populated before allow the document to be emailed.

Private Sub email123()

' email123 Macro

Dim Cell As Range
For Each Cell In Range("mandatory_fields")

If Cell.Value = "" Then
MsgBox "Please complete all mandatory fields."
Exit For
Else
MsgBox "All mandatory fields have been completed. Will now send
template."
ActiveWorkbook.SendMail Recipients:="intended email address",
Subject:="RFC : " & Range("b9")

End
End If
Next
End Sub

However, my range (mandatory_fields) can only cover a certain number of
fields (6 i think) and when validating, if the first cell in the range
is populated - validation is accepted, even though the other cells in
the range are not.

Does this make sense and if so where am I going wrong.
 
Hi,

Change your macro to this:

Private Sub email123()

' email123 Macro

Dim Cell As Range
Dim bInComplete as Boolean
bInComplete=False
For Each Cell In Range("mandatory_fields")
If Cell.Value = "" Then
MsgBox "Please complete all mandatory fields."
bIncomplete=True
Exit For
End If
Next
If Not BinComplete Then
MsgBox "All mandatory fields have been completed. Will now
send
template."
ActiveWorkbook.SendMail Recipients:="intended email
address",
Subject:="RFC : " & Range("b9")
End If
End Sub


Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Thanks for that.

However, I've tried that and when all the fields are populated the
"please complete all mandatory data" msgbox come up.
 
Mark,

Your logic is wrong.

The procedure fails (that is, sends the email)if the first
cell has a value because on the first pass of the For loop
the else part of the condition is executed and your
instructions are to send the email.

You need to separate the condition from the loop.

Compare your procedure with the following:
_________________________________________________________
Dim Cell As Range
Dim Flag As Boolean

Flag = True

For Each Cell In Range("mandatory_fields")

If Cell.Value = "" Then
Flag = False
MsgBox "Please complete ALL mandatory fields."
Exit For
End If

Next

If Flag = True Then
MsgBox "All mandatory fields have been completed. Will now
send
template."
ActiveWorkbook.SendMail Recipients:="intended email
address",
Subject:="RFC : " & Range("b9")
End If

End Sub
___________________________________________________________
HTH
Cheers, Ian
 
Ian,

Thanks for that. However, I've tried your' idea and now even when the
mandatory fields are populated the msgbox still states "please complete
all mandatory data" and the email won't send.
 
I don't think it's your macro. I think your range is bigger than you think.

Option Explicit
Private Sub email123()
' email123 Macro

Dim Cell As Range
Dim bInComplete As Boolean
bInComplete = False
For Each Cell In Range("mandatory_fields")
If Cell.Value = "" Then
MsgBox "Please complete all mandatory fields." _
& vbLf & "Including: " & Cell.Address(0, 0)
bInComplete = True
Exit For
End If
Next
If Not bInComplete Then
MsgBox "All mandatory fields have been completed." _
& " Will now send template."
ActiveWorkbook.SendMail Recipients:="intended email Address ", _
Subject:="RFC : " & Range("b9")
End If

End Sub

(I indented the code and added the address of the first cell to fail to the
msgbox. It may help you find the culprit.)
 
Thanks for that. However, when i try this it references the middle of a
merged cell. Is this where i'm going wrong.
 
Back
Top