Help with IF Statement

[jason_kelly]

Hi There,

I have the following code below, that will basically execute a function based on wether or not if there are specific fields on a form which are filled out.

The problem is this, when the fields (h3, h9 and h5) are all filed in, I have 2 message boxes that are appearing and popping up:

"V4_SEARCHBYASSIGNTO" and
"V4_SEARCHBYASSIGNTO_WTYPE"

The only message box that should be popping up is:
"V4_SEARCHBYASSIGNTO_WTYPE"

Any help with this is greatly appreciated.

Cheers,

Jay








Private Sub btn_search_Click()
'V4_SEARCHBYFILE
'V4_SEARCHBYSUB
'V4_SEARCHBYASSIGNTO
'V4_SEARCHBYASSIGNTO_WTYPE

If frmIMTS.h1.Value <> "" Then
MsgBox "V4_SEARCHBYFILE"
End If

If frmIMTS.h12.Value <> "" Then
MsgBox "V4_SEARCHBYSUB"
End If

If frmIMTS.h5.Value <> "" And frmIMTS.h9.Value <> "" Then
MsgBox "V4_SEARCHBYASSIGNTO"
End If

If frmIMTS.h3.Value <> "" And frmIMTS.h9.Value <> "" And frmIMTS.h5.Value <> "" Then
MsgBox "V4_SEARCHBYASSIGNTO_WTYPE"
End If

End Sub

[Bob Phillips]

Is this what you mean



Private Sub btn_search_Click()
'V4_SEARCHBYFILE
'V4_SEARCHBYSUB
'V4_SEARCHBYASSIGNTO
'V4_SEARCHBYASSIGNTO_WTYPE

If frmIMTS.h1.Value <> "" Then
MsgBox "V4_SEARCHBYFILE"
End If

If frmIMTS.h12.Value <> "" Then
MsgBox "V4_SEARCHBYSUB"
End If

Select Case True
Case frmIMTS.h5.Value <> "" And frmIMTS.h9.Value <> ""

If frmIMTS.h3.Value <> "" Then

MsgBox "V4_SEARCHBYASSIGNTO"
Else

MsgBox "V4_SEARCHBYASSIGNTO_WTYPE"
End If

Case frmIMTS.h3.Value <> "" And frmIMTS.h9.Value <> "" And frmIMTS.h5.Value <> ""

MsgBox "V4_SEARCHBYASSIGNTO_WTYPE"
End Select
End If
End Sub

[jason_kelly]

Thanks XLD, but im still getting the message box: V4_SEARCHBYASSIGNTO" when all 3 fields are filled out, not sure why excel is not checking the last case but stopping at the second. Im using your code but modified below:



Private Sub btn_search_Click()
'V4_SEARCHBYFILE
'V4_SEARCHBYSUB
'V4_SEARCHBYASSIGNTO
'V4_SEARCHBYASSIGNTO_WTYPE

If frmIMTS.h1.Value <> "" Then
MsgBox "V4_SEARCHBYFILE"
End If

If frmIMTS.h12.Value <> "" Then
MsgBox "V4_SEARCHBYSUB"
End If

Select Case True

Case frmIMTS.h5.Value <> "" And frmIMTS.h9.Value <> ""
MsgBox "V4_SEARCHBYASSIGNTO"

Case frmIMTS.h3.Value <> "" And frmIMTS.h9.Value <> "" And frmIMTS.h5.Value <> ""
MsgBox "V4_SEARCHBYASSIGNTO_WTYPE"

End Select
End Sub

[Bob Phillips]

Can you post the workbook?

[shrivallabha]

Try NOT operator for 3rd Expression like:
If frmIMTS.h5.Value <> "" And frmIMTS.h9.Value <> "" And Not(frmIMTS.h3.Value <> "") Then
MsgBox "V4_SEARCHBYASSIGNTO"
End If


And for the 4th expression use concatenation operator like:

If (frmIMTS.h3.Value <> "" & frmIMTS.h9.Value <> "" & frmIMTS.h5.Value <> "") = True Then
MsgBox "V4_SEARCHBYASSIGNTO_WTYPE"
End If