Solved If statement not executing properly in ByVal Target As Range

October 31, 2018 at 09:17:17
Specs: Windows 7
This code checks cell ranges based on the criteria provided in the Worksheet_Change(ByVal Target As Range). It works for the most part except for this one anomaly. When range SalesPrice changes it checks the loanamount value, "If my loan amount is under 271,050 and checkbox1.value is true" it goes to the next routine (this is correct behavior). But if I change the SalesPrice which re-calculates LoanAmount to a number that causes the LoanAmount to go over 271050 the MsgBox code does not execute. But if I retype the same SalesPrice 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 for the code to detect that the LoanAmount is too high.
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

See More: If statement not executing properly in ByVal Target As Range

Reply ↓  Report •

October 31, 2018 at 11:11:16
Have you Single Stepped through the code to see exactly what it is doing when it works vs. when it doesn't?

Without having your exact spreadsheet (and all the rest of the code) it's kind of hard for us to reproduce your issue.

Single Stepping might reveal the issue.

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

Reply ↓  Report •

October 31, 2018 at 11:13:02
I don't think you can single step when it is a "ByVal Target As Range" because it only executes if you change the cell.

Reply ↓  Report •

October 31, 2018 at 11:43:32
✔ Best Answer
Place your cursor in any executable line of your code and press F9 or click in the grey bar next to an instruction. A brown dot will appear. That is called a Break Point.

Yes, the code will run automatically when you change a cell, but it will stop at the break point. From there, you can use F8 to Single Step.

Add some Watches and you should be able to see exactly what your code is doing.

One item of clarification:

You said "it only executes if you change the cell."

That is not exactly true. A Worksheet_Change macro will execute (run) when you change any cell or cells. How else would it execute the typical first instruction to know if it should do anything else?

If Target.Address = "$D$5" Then

In fact, if any of the instructions in your code change a cell, the code will jump back to the beginning and start again. You may even see that as you single step through the code. That is why it is advisable to disable events before the code changes a cell and then re-enable them afterwards. This will prevent the code from starting over, which can sometimes cause issues.

However, you must be very careful when you do this. If you disable events and the code fails or you break out of the code before events are enabled again, no event code - across the entire application - will run. It is for that reason that you should use an error handling routine to re-enable events before the code exits.

You will end up pulling your hair out when all of your event code stops working because there is no outward sign that events are disabled.

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

Reply ↓  Report •
Related Solutions

Ask Question