Msg Box appear many times

[MichaelRSnow]

I have the following piece of code, it bring up a message when an option button is selected and the value of a cell is greater that 75%, the issue is every time you hit return within any another cell through out the document the message appears again. This is just a warning message that doesn't force a value to be changed so should only appear the once?
Any help to achieve this outcome would be more than welcome?

'New Mortgage Non NBS Cap Warning Messages'
If OptionButton24 = True And Range("M26") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M26").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"

 

[Ruddles]

Does this make sense?

No, not as it stands. I'd need to see where the code was currently located, i.e. which event handler it was being called from.

Go with Charles - it's pointless two people trying to help and possibly going off in different directions.

 

[Ruddles]

Dim [B][COLOR=red]mem[/COLOR][/B] As Range
On Error GoTo Worksheet_Change_Err
If Target.Dependents.Address <> "" Then
    For Each [B][COLOR=red]meme[/COLOR][/B] In Target.Dependents

Oops! Not that this will have affected the results though.

 

[CharlesChuckieCharles]

Thats an in blog edit, oops, but the MEME in the M20 M22 get out clause is also wrong it should be Target

But overall it would have been good to know at the start that there where going to be mirrad options to process through this change event

Also it's considered poor practise to have stuff magically appear in user interfaces, better to have it on display, but disabled.

effectively if you want to "Sense" the change in a calculated field it has to be through the input cells of which it is a dependant.


I think thats me out

 

[MichaelRSnow]

No that works fine, its the MEME in the M20 M22 get out clause which is wrong

You have kind of lost me now if i'm honest...

This is my code for all of the option button, some have option button, some don't but the % value is always calculated by the inputted values from 2 other cells!

This is very frustrating I must admit

Private Sub Worksheet_Calculate()
'Remortgage Cap Warning Messages'
If Range("M19") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M19").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Further Advance Cap Warning Messages'
If OptionButton22 = True And Range("M30") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M30").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE with Further Advance Cap Warning Messages'
If OptionButton26 = True And Range("M30") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M30").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer wants to change the repayment method of their main loan via the TofE then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Capital Raising Cap Warning Messages'
If Range("M33") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M33").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'New Mortgage Existing Borrower Cap Warning Messages'
If OptionButton6 = True And Range("M26") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M26").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer is porting their existing balance, and this balance is already on Interest Only or Part and Part above 75% LTV, then they can continue to port it on the existing repayment method, any additional can only proceed on a Repayment basis" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'New Mortgage Non NBS Cap Warning Messages'
If OptionButton24 = True And Range("M26") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M26").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Switching Cap Warning Messages'
If Range("M36") > 75 Then
MsgBox "Switchers are unaffected by the new 75% LTV Cap, customer can ONLY change their repayment method via a SF211 Form" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M36").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE and Switch Cap Warning Messages'
If OptionButton27 = True And Range("M39") > 75 Then
MsgBox "Switchers are unaffected by the new 75% LTV Cap, customer can ONLY change their repayment method via a SF211 Form" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M39").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer wants to change the repayment method of their main loan via the TofE application then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE Only Cap Warning Messages'
If OptionButton23 = True And Range("M39") > 75 Then
MsgBox "If the customer wants to change the repayment method of their main loan via the TofE application then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M39").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
End Sub

 

[MichaelRSnow]

No that works fine, its the MEME in the M20 M22 get out clause which is wrong

You have kind of lost me now if i'm honest...

This is my code for all of the option button, some have option button, some don't but the % value is always calculated by the inputted values from 2 other cells!

This is very frustrating I must admit

Private Sub Worksheet_Calculate()
'Remortgage Cap Warning Messages'
If Range("M19") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M19").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Further Advance Cap Warning Messages'
If OptionButton22 = True And Range("M30") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M30").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE with Further Advance Cap Warning Messages'
If OptionButton26 = True And Range("M30") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M30").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer wants to change the repayment method of their main loan via the TofE then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Capital Raising Cap Warning Messages'
If Range("M33") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M33").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'New Mortgage Existing Borrower Cap Warning Messages'
If OptionButton6 = True And Range("M26") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M26").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer is porting their existing balance, and this balance is already on Interest Only or Part and Part above 75% LTV, then they can continue to port it on the existing repayment method, any additional can only proceed on a Repayment basis" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'New Mortgage Non NBS Cap Warning Messages'
If OptionButton24 = True And Range("M26") > 75 Then
MsgBox "The LTV is greater than 75%, the new LTV Cap Rules will apply and the case can only proceed on a Repayment basis, alternatively the loan amount can be reduced." & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M26").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'Switching Cap Warning Messages'
If Range("M36") > 75 Then
MsgBox "Switchers are unaffected by the new 75% LTV Cap, customer can ONLY change their repayment method via a SF211 Form" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M36").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE and Switch Cap Warning Messages'
If OptionButton27 = True And Range("M39") > 75 Then
MsgBox "Switchers are unaffected by the new 75% LTV Cap, customer can ONLY change their repayment method via a SF211 Form" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M39").Value, "#,##0.00") & vbLf & vbLf & _
"If the customer wants to change the repayment method of their main loan via the TofE application then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
'TofE Only Cap Warning Messages'
If OptionButton23 = True And Range("M39") > 75 Then
MsgBox "If the customer wants to change the repayment method of their main loan via the TofE application then they can only convert to Interest Only of Part & Part if the main loan is below 75% LTV as per the new Cap rules" & vbLf & vbLf & _
"The LTV is currently = " & Format(Range("M39").Value, "#,##0.00") & vbLf & vbLf & _
"See Office Instruction 13/11 - LTV Cap for Interest Only & Part and Part Mortgages for more details"
End If
End Sub

 

[CharlesChuckieCharles]

The easy way is to take all of this out of a sheet sensitive module into a standard module and create a button to press which triggers the code via its macro setting

and add to your module code

application enableevents=false ( the true at the end )
but just before setting true update A title cell to "Re-Calculated " & format(now(),"DD-MMM-YYYY HH:NN:SS")


then in worksheet_Change reset a title cell to "Uncalculated"

so if any changes occur the title wil say uncalculated but when you press your Button it will update stuff and check stuff without going into endless calculation loops etc