This Macro excerpt is based on a "Check Cell Content before continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements (Conditions
3, & 2 are met first) then the Macro continues even though the 1st condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

Do you want AND instead of OR?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
This Macro excerpt is based on a "Check Cell Content before continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements
(Conditions
3, & 2 are met first) then the Macro continues even though the 1st
condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

If any of these conditions exist, then I want the message. Only if all are
met do I want to continue the macro. I believe that means I need an "or"
function.

"Don Guillett" wrote:

Do you want AND instead of OR?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
This Macro excerpt is based on a "Check Cell Content before continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements
(Conditions
3, & 2 are met first) then the Macro continues even though the 1st
condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

I just tested this and it worked fine. Perhaps you don't want = but do want
< (NOT equal)

Sub checkit()
If Range("h8").Value = "F" Or _
Range("h6").Value = Range("h7").Value Or _
Range("h4").Value = Range("H2").Value _
Then
MsgBox "dont do it"
Exit Sub
Else
MsgBox "doit"
'Do macro.
End If

End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
If any of these conditions exist, then I want the message. Only if all are
met do I want to continue the macro. I believe that means I need an "or"
function.

"Don Guillett" wrote:

Do you want AND instead of OR?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
This Macro excerpt is based on a "Check Cell Content before
continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements
(Conditions
3, & 2 are met first) then the Macro continues even though the 1st
condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

Thank You, that does appear to work.

Another solution I came up with was to simply add an "IF(OR..." function, in
a variables area, that provides a Yes/No output. I then changed the "If"
statement in my code to look only at that cell. (If Range("BZ28").Value =
"NO" _)

Now I need to decide which will make more sense to me when I try to edit
this in 6 months.

Thanks again,
Christopher McCune

"Don Guillett" wrote:

I just tested this and it worked fine. Perhaps you don't want = but do want
< (NOT equal)

Sub checkit()
If Range("h8").Value = "F" Or _
Range("h6").Value = Range("h7").Value Or _
Range("h4").Value = Range("H2").Value _
Then
MsgBox "dont do it"
Exit Sub
Else
MsgBox "doit"
'Do macro.
End If

End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
If any of these conditions exist, then I want the message. Only if all are
met do I want to continue the macro. I believe that means I need an "or"
function.

"Don Guillett" wrote:

Do you want AND instead of OR?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
This Macro excerpt is based on a "Check Cell Content before
continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements
(Conditions
3, & 2 are met first) then the Macro continues even though the 1st
condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

I would stay within the macro but if you insist on doing that why not one
cell
if(a=a,1,0)+if(b=b,1,0)
then
if range("a2")<3 then msgbox "bad boy"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
Thank You, that does appear to work.

Another solution I came up with was to simply add an "IF(OR..." function,
in
a variables area, that provides a Yes/No output. I then changed the "If"
statement in my code to look only at that cell. (If Range("BZ28").Value =
"NO" _)

Now I need to decide which will make more sense to me when I try to edit
this in 6 months.

Thanks again,
Christopher McCune

"Don Guillett" wrote:

I just tested this and it worked fine. Perhaps you don't want = but do
want
< (NOT equal)

Sub checkit()
If Range("h8").Value = "F" Or _
Range("h6").Value = Range("h7").Value Or _
Range("h4").Value = Range("H2").Value _
Then
MsgBox "dont do it"
Exit Sub
Else
MsgBox "doit"
'Do macro.
End If

End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
If any of these conditions exist, then I want the message. Only if all
are
met do I want to continue the macro. I believe that means I need an
"or"
function.

"Don Guillett" wrote:

Do you want AND instead of OR?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris T-M" wrote in message
...
This Macro excerpt is based on a "Check Cell Content before
continuing..."
discussion with Bill Renaud back in October.

I works great (thanks Bill) with one exception.

If the cells are changed in reverse order of my "Or" statements
(Conditions
3, & 2 are met first) then the Macro continues even though the 1st
condition
has not been met.

Every other combination works.

Thanks in advance for any help on this. (Code Below)

'/// Check for Required Fields (Project Number)
If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox "Please fill in the Abbreviation, Project Type, Class, and
Application Date fields (blue font with an *)", _
vbCritical + vbOKOnly, _
"You have not filled in all the fields required to
generate a
complete project number."
Exit Sub
Else
'Do macro.
End If

Hi Chris,

Sounds like you are still having basic logic problems. In this situation, I
normally revert back to a "truth table" concept to sort out all of the
combinations before attempting to write the program. It would be something
like this (using 'short-form' notation):

Situation Action
---------------------- --------
Q44.Value="Fill in..." MsgBox
F47 is empty MsgBox
H56 is empty MsgBox
F47.Value=AB2.Value Do macro
H56.Value=AH2.Value Do macro

Remember that empty cells are considered to be 0, so if F47 is empty
(hasn't been filled in by the user yet) and AB2 =0, then they will be
considered equal by Excel. Complete the table as I have suggested above and
post it here, then we'll be able to fix the logic.

I'm suspecting that you probably need a separate If statement to check the
value of Q44 first and display the MsgBox, regardless of the state of the
other 2 cells. The situation with the other 2 cells (F47 and H56) is
probably more complex. You might need a separate Boolean variable to keep
track of everything, then finally show the MsgBox and exit if the Boolean
variable (blnShowMsgBox?) is TRUE.

--
Regards,
Bill Renaud

Never mind the post immediately above; it is incomplete. I should have
expanded it out to something like the following (hyphen means "don't
care"):

Q44 F47 H56 Action
-------------- ----- ----- ------
="Fill in..." - - MsgBox
- empty - MsgBox
- - empty MsgBox
- =AB2 - MsgBox
- - =AH2 MsgBox
<"Fill in..." <AB2 <AH2 Do macro

Chris T-M wrote (in another post):
<<If any of these conditions exist, then I want the message. Only if all
are met do I want to continue the macro.

This appears to be contradictory. If any conditions exist (OR), then show
the message. If all are met (AND) then do the macro. This does not make
sense to me.

As an alternative, I sometimes use a program structure like the following
(or declare ShowMsgBox to be an error handler and use Err.Raise):

'----------------------------------------------------------------------
Sub CheckCellContent()
Dim strMsgBox As String

If Range("Q44").Value = "Fill in..." Then GoTo ShowMsgBox
If IsEmpty(Range("F47")) Then GoTo ShowMsgBox
If IsEmpty(Range("H56")) Then GoTo ShowMsgBox

If OtherConditions _
Then
'Do macro.
End If

GoTo ExitSub

ShowMsgBox:
strMsgBox = "You have not filled in all" & vbNewLine & _
"fields required to generate" & vbNewLine & _
"a complete project number." & vbNewLine & _
vbNewLine & _
"Please fill in the Abbreviation," & vbNewLine & _
"Project Type, Class, and Application" & vbNewLine & _
"Date fields (blue font with an *)."

MsgBox strMsgBox, _
vbCritical + vbOKOnly, _
"Incomplete Project Number"
ExitSub:
End Sub

--
Regards,
Bill Renaud

OK you lost me. Did I mention I am new to VBA?
That being said, the logic I used in the Macro works if used as a Function,
so why wouldn't it work in a Macro (programming language asside)?

Regardless of my understanding, setting this up as an "On Error GoTo..."
does work.

As for my previous contridiction, I appologize. What I meant to say was:
If any are true, then show message.
If all are false, then do macro.

For the Macro Logic (broken out):
If Range("Q44").Value = "Fill in..." Then GoTo ShowMsgBox
If Range("F47").Value = Range("AB2").Value Then GoTo ShowMsgBox
If Range("H56").Value = Range("AH2").Value Then GoTo ShowMsgBox

The Value = Value cells are text values. Both effectively contain
instructions for what data is desired unless one of them is filled in. If one
is filled in, then I can concatelate a statement such as "The name of the new
project is" "NewProj".

Thank you for the additional advice,
Chris T-M


"Bill Renaud" wrote:

Never mind the post immediately above; it is incomplete. I should have
expanded it out to something like the following (hyphen means "don't
care"):

Q44 F47 H56 Action
-------------- ----- ----- ------
="Fill in..." - - MsgBox
- empty - MsgBox
- - empty MsgBox
- =AB2 - MsgBox
- - =AH2 MsgBox
<"Fill in..." <AB2 <AH2 Do macro

Chris T-M wrote (in another post):
<<If any of these conditions exist, then I want the message. Only if all
are met do I want to continue the macro.

This appears to be contradictory. If any conditions exist (OR), then show
the message. If all are met (AND) then do the macro. This does not make
sense to me.

As an alternative, I sometimes use a program structure like the following
(or declare ShowMsgBox to be an error handler and use Err.Raise):

'----------------------------------------------------------------------
Sub CheckCellContent()
Dim strMsgBox As String

If Range("Q44").Value = "Fill in..." Then GoTo ShowMsgBox
If IsEmpty(Range("F47")) Then GoTo ShowMsgBox
If IsEmpty(Range("H56")) Then GoTo ShowMsgBox

If OtherConditions _
Then
'Do macro.
End If

GoTo ExitSub

ShowMsgBox:
strMsgBox = "You have not filled in all" & vbNewLine & _
"fields required to generate" & vbNewLine & _
"a complete project number." & vbNewLine & _
vbNewLine & _
"Please fill in the Abbreviation," & vbNewLine & _
"Project Type, Class, and Application" & vbNewLine & _
"Date fields (blue font with an *)."

MsgBox strMsgBox, _
vbCritical + vbOKOnly, _
"Incomplete Project Number"
ExitSub:
End Sub

--
Regards,
Bill Renaud

<<OK you lost me.
Where did we lose you? (We are the ones that are lost!!! :) )

<<...the logic I used in the Macro works if used as a Function, so why
wouldn't it work in a Macro?
I think you have this backwards. In general, everything will work in a
macro, but will not necessarily work in a function. For example, you can
set other cells (run your macro after deciding that everything is OK),
insert new worksheets into the workbook, run other commands, but you can't
do those things if the function were called from a formula in a worksheet
cell.

<<What I meant to say was:
If any are true, then show message.
If all are false, then do macro.

Then the logic in your original post should work, as that is exactly what
it does. It will check all 3 conditions, and if ANY of the 3 are TRUE, then
it will display the MsgBox and exit. So, if Q44 has some other value in it
(i.e. "Project X"), but F47 is equal to AB2, then your macro will run (the
Else part of the If statement).

If Range("Q44").Value = "Fill in..." Or _
Range("F47").Value = Range("AB2").Value Or _
Range("H56").Value = Range("AH2").Value _
Then
MsgBox ...
Exit Sub
Else
'Do macro.
End If
--
Regards,
Bill Renaud