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"

 

[CharlesChuckieCharles]

Try puting this is the sheet level module

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("M26")) Is Nothing Then
    Exit Sub
ElseIf OptionButton24 = True And Target.Value > 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(Target.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]

Try puting this is the sheet level module

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Application.Intersect(Target, Range("M26")) Is Nothing Then
    Exit Sub
ElseIf OptionButton24 = True And Target.Value > 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(Target.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

Cheers Buddy

The msg box do not appear at all now? any ideas why?

 

[CharlesChuckieCharles]

OptionButton24

maybe me.OptionButton24 or form1.OptionButton24

But stepping through the macro should tell you why or at least where

 

[Ruddles]

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.

You're evidently executing the code repeatedly.

If it's in a section of code which is executed more than once and those conditions are true, you will get multiple message boxes.

If you only want it to appear once, you need to place it where it will only be triggered once, typically when the condition first arises. In this case it could be when OptionButton24 is clicked or when M26 exceeds its threshhold value.

So the $64,000 question is: where is this code actually located?

 

[CharlesChuckieCharles]

Cheers Buddy

The msg box do not appear at all now? any ideas why?

Apologies

i've only ever done this on active cell change, Not on calculated dependant so i've learnt something NEW

TRY

Public Sub Worksheet_Change(ByVal Target As Range)
 
Dim mem As Range
On Error GoTo Worksheet_Change_Err
If Target.Dependents.Address <> "" Then
    For Each meme In Target.Dependents
 
        If Not Application.Intersect(meme, Range("M26")) Is Nothing Then
            If OptionButton24 = True And Target.Value > 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(meme.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 If
 
    Next meme
End If
Exit Sub
 
Worksheet_Change_Err:
Exit Sub
End Sub

what it does is, it checks any cell that changes, then checks that any of that cells dependant cells are M26 then checks the option and value

 

[MichaelRSnow]

Apologies

i've only ever done this on active cell change, Not on calculated dependant so i've learnt something NEW

TRY

Public Sub Worksheet_Change(ByVal Target As Range)
 
Dim mem As Range
On Error GoTo Worksheet_Change_Err
If Target.Dependents.Address <> "" Then
    For Each meme In Target.Dependents
 
        If Not Application.Intersect(meme, Range("M26")) Is Nothing Then
            If OptionButton24 = True And Target.Value > 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(meme.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 If
 
    Next meme
End If
Exit Sub
 
Worksheet_Change_Err:
Exit Sub
End Sub

what it does is, it checks any cell that changes, then checks that any of that cells dependant cells are M26 then checks the option and value

Hi there<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Unfortunately that doesn't appear to work either<o></o>
<o></o>
when I select 'OptionButton24' it opens up rows 20, 22 and 26, when i then keys values into M20 and M22 this calculates the % value of M26, if M26 is over 75% I want the error message to appear the once?<o></o>
Does this make sense?<o></o>

 

[MichaelRSnow]

You're evidently executing the code repeatedly.

If it's in a section of code which is executed more than once and those conditions are true, you will get multiple message boxes.

If you only want it to appear once, you need to place it where it will only be triggered once, typically when the condition first arises. In this case it could be when OptionButton24 is clicked or when M26 exceeds its threshhold value.

So the $64,000 question is: where is this code actually located?

Thanks Ruddles as i have just posted to Charles

When I select 'OptionButton24' it opens up rows 20, 22 and 26, when i then keys values into M20 and M22 this calculates the % value of M26, if M26 is over 75% I want the error message to appear the once?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Does this make sense?

 

[CharlesChuckieCharles]

Now we're at how to make it just come up once when its dependant on two cells?

But the option buttton can be ditched from the code since it's irrelevant


But

Public Sub Worksheet_Change(ByVal Target As Range)
 
Dim mem As Range
On Error GoTo Worksheet_Change_Err
 
[B]       If Not Application.Intersect(meme, Range("M20,M22")) Is Nothing Then exit sub [/B]
 
 
 
 
If Target.Dependents.Address <> "" Then
    For Each meme In Target.Dependents

If OptionButton24 = True And Target.Value > 75 Then

NOW

If Target.Value > 75 Then




might help

 

[MichaelRSnow]

Now we're at how to make it just come up once when its dependant on two cells?

But the option buttton can be ditched from the code since it's irrelevant


But

Public Sub Worksheet_Change(ByVal Target As Range)
 
Dim mem As Range
On Error GoTo Worksheet_Change_Err
 
[B]      If Not Application.Intersect(meme, Range("M20,M22")) Is Nothing Then exit sub [/B]
 
 
 
 
If Target.Dependents.Address <> "" Then
    For Each meme In Target.Dependents

If OptionButton24 = True And Target.Value > 75 Then

NOW

If Target.Value > 75 Then




might help

I have 10 different option buttons, each option button drives a slightly different version of msg box wording. So I can't really ditch the use of the option button.