0

I am new to VBA. I am having an issue with a MsgBox and I can't seem to find the answer. So heres the explanation. If the value of a certain cell is <0 then the user is trying to issue payments over a maximum amount allowable (which on a very rare occasion has to happen). I have created a pop up to ask Yes/No to continue with the payment. If they press NO then it undoes the last entry and they can reenter the payment.

But if they click YES the pop-up goes away which is what I want, but it keeps popping up anytime you try and do anything else on the spreadsheet because the value continues to be <0. How do I make the msgbox stop completely when they press yes?

This is my code so far: 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
  If Range("C39") < 0 Then 
    If MsgBox("Total cash flow/payout cannot exceed maximum contract amount" & vbCrLf & "Do you want to continue?", vbYesNo + vbCritical, "WARNING") = vbNo Then 
     Application.Undo 
    End If 
End Sub
Community
  • 1
  • 1
Tamara
  • 1
  • 2
  • I'd use either a cell or a defined name that you can put some text (e.g. "Verified") into, then your code can test for that before providing the message, and simply exit if the amounts have already been verified. – Rory Mar 16 '15 at 14:59
  • A minor semantic issue: I'd change `cannot` to `should not` in your warning message, since the whole point of the message box is to ask if it's OK to continue despite the questionable overpayment. – FreeMan Mar 16 '15 at 16:12

1 Answers1

1

There are a number of ways to work this code wise, but from the business side, I would suggest that when they select Yes that you capture their selection and annotate that for your own records.

That would also allow you to use that past selection in your top level if statement.

For example, if you set Range("40") to "Allowable" when yes was selected, you could change to say

If Range("C39") < 0 AND Range("C40") <> "Allowable" Then
     If MsgBox("Total cash flow/payout cannot exceed maximum contract amount" & vbCrLf & "Do you want to continue?", vbYesNo + vbCritical, "WARNING") = vbNo Then 
          Application.Undo
     Else
          Range("C40").Value = "Allowable" 
     End If 
End If
GMLewisII
  • 366
  • 1
  • 7
  • So I am all self taught on VBA so I think I understand this part of the code, do I need to change the MsgBox part of the code as well?? Sorry. I feel like im a kindergartner asking a university student for help with my colouring book. lol!!! – Tamara Mar 16 '15 at 18:52
  • You're good Tamara, we all have to start somewhere! I began my programming career by learning VBA and ended up going back to school for programming. I'm editing the code above to include setting the Range("40") to allowable – GMLewisII Mar 17 '15 at 12:42