Solved If statement ignored in worksheet change byval routine

October 30, 2018 at 05:19:02
Specs: Windows 7
Can anyone help me correct this code? I have it in the Worksheet_Change(ByVal Target As Range) but it only works for the if statement that evaluates the SalesPrice value of 317646.
Even if i rearrange the if statement it won't execute the LoanAmount if statement.

If Target.Address = "$D$5" Then 'Sales Price
If Range("LoanProgram").Value = "HFA Bond Miami FHA" And 
Range("LoanProgram").Value = "HFA Bond Miami Conv" Or Range("SalesPrice").Value > 317646 Then
MsgBox "Max Sales Price is $317,646"
End If
If Range("LoanAmount").Value > 271050 & Sheets("Main").CheckBox1.Value = True Then
MsgBox "Max Loan Amount is $271,050"
Sheets("Main").CheckBox1.Value = False
End If
Call PushTo105Button
Call Calc_MI
Range("E6").Value = Range("D6").Value
End If

message edited by mecerrato


See More: If statement ignored in worksheet change byval routine

Reply ↓  Report •

✔ Best Answer
October 30, 2018 at 16:10:14

If Range("LoanProgram").Value Like "*HFA Bond Miami*" And _
   Range("SalesPrice").Value > 317646 Then

message edited by DerbyDad03



#1
October 30, 2018 at 08:49:54
I got the correction from stack overflow forum, I had to change the "&" to "And" and it fixed it, arghhh rookie mistake.

Don't know how to mark myself as the answer for other users searching for this issue.

message edited by mecerrato


Reply ↓  Report •

#2
October 30, 2018 at 09:03:58
Do you use indents in your code? If not, you should. It would make it much easier to read.

  If Target.Address = "$D$5" Then 'Sales Price
      If Range("LoanProgram").Value = "HFA Bond Miami FHA" And _
         Range("LoanProgram").Value = "HFA Bond Miami Conv" Or _
         Range("SalesPrice").Value > 317646 Then
          MsgBox "Max Sales Price is $317,646"
      End If
     
      If Range("LoanAmount").Value > 271050 & Sheets("Main").CheckBox1.Value = True Then
          MsgBox "Max Loan Amount is $271,050"
          Sheets("Main").CheckBox1.Value = False
      End If
     
    Call PushTo105Button
    Call Calc_MI
    Range("E6").Value = Range("D6").Value
  End If

That said (and done) I need an explanation of these 3 things:

1 - This instruction is not valid.

If Range("LoanProgram").Value = "HFA Bond Miami FHA" And 
Range("LoanProgram").Value = "HFA Bond Miami Conv" Or Range("SalesPrice").Value > 317646 Then

This will never work. It's not valid syntax for the VBA editor. Bright Red.

Did you mean this?

      If Range("LoanProgram").Value = "HFA Bond Miami FHA" And _
         Range("LoanProgram").Value = "HFA Bond Miami Conv" Or _
         Range("SalesPrice").Value > 317646 Then

2 - If that is what you meant to use, then I'm pretty sure that the "And" will never be True. How could Range("LoanProgram") contain these 2 values at the same time?

HFA Bond Miami FHA
HFA Bond Miami Conv

3 - What is this supposed to do?

If Range("LoanAmount").Value > 271050 & Sheets("Main").CheckBox1.Value = True Then

I don't do much of anything with CheckBoxes. Is the use of the & equivalent to And as opposed to being used as the concatenation operator?

   If Range("LoanAmount").Value > 271050 And _
      Sheets("Main").CheckBox1.Value = True Then

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
October 30, 2018 at 14:10:42
hmmmm, here is what I need it to do:

if the sales price is > $317,646 and the loan program is either "HFA Bond Miami FHA" or "HFA Bond Miami Conv" then msgbox the user telling them the sales price is too high.

the second part (for any loan program)
is to test if checkbox1.value =true and if so, then test for loan amount to see if it is greater than $271,050 and if so then msgbox the user telling them the loan amount is too high and change checkbox1.value to false


Reply ↓  Report •

Related Solutions

#4
October 30, 2018 at 16:10:14
✔ Best Answer

If Range("LoanProgram").Value Like "*HFA Bond Miami*" And _
   Range("SalesPrice").Value > 317646 Then

message edited by DerbyDad03


Reply ↓  Report •

#5
October 30, 2018 at 18:32:31
DerbyDad03 can you help me with this annoying issue? This code checks for some of the stuff you helped me with. It works for the most part except for this one anomaly. If my loan amount is under 271,050 and checkbox1.value is true and then I change the sales price to a number that causes the LoanAmount to go over 271050 the MsgBox code does not execute. But if I retype the same sales price again it does execute. So in order for it to work properly I have to type in the SalesPrice twice if chkbox1.value was initially checked.

If Target.Address = "$D$5" Then 'Sales Price
If Range("LoanProgram").Value Like "*HFA Bond Miami*" And _
   Range("SalesPrice").Value > 317646 Then MsgBox "Miami-Dade Bond Max Sales Price is $317,646"
If Range("LoanAmount").Value > 271050 And Sheets("Main").CheckBox1.Value = True Then
MsgBox "MDEAT Max Loan Amount is $271,050"
Sheets("Main").CheckBox1.Value = False
End If


Reply ↓  Report •

Ask Question