Drop down list value sets off a macro but wont let me go back and change the value in the drop down list

[Darkshine]

Good morning folks.

Wonderful to be part of this community.

I was hoping someone could answer my question.

I have a list of values (3) in cell P53 of a worksheet. When "Yes - Full" is the active selection from that list, a macro runs which essentially makes sure there is a formula in cell Q53. The problem I am having is, if I now choose to go back to cell P53 to change the value, it won't let me. It seems to immediately run the macro again. What do I need to add to my code to avoid this issue?

my code is thus:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "Yes - Full" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Call pension1
Application.EnableEvents = True
Application.ScreenUpdating = True
Else
End If
End Sub

Thank you in advance kind and brainy people!

 

[JoeMo]

also... if P44 = "Yes - full" then macro to save a different formula to Q54
also... if P45 = "Yes - full" then macro to save a different formula to Q55
also... if P46 = "Yes - full" then macro to save a different formula to Q56

does that make sense?

Yes, it makes sense, but I would need to know what exactly the formulas you want to enter in those cells are and for each formula which cell in col Q you want to enter it in. And, ... do you still want to call the routine pension1 as does the code in your OP???????

 

[Darkshine]

Yes, it makes sense, but I would need to know what exactly the formulas you want to enter in those cells are and for each formula which cell in col Q you want to enter it in. And, ... do you still want to call the routine pension1 as does the code in your OP???????

Sorry Joe,

if P44 = "yes - full" will call on pension2 in cell q54
if p45 = "yes - full" will call on pension3 in cell q55
if p46 = "yes - full" will call on pension4 in cell q56

I realise I shouldn't have mentioned "formulas" at all, as they are part of the macros

Thanks Joe

 

[JoeMo]

Sorry Joe,

if P44 = "yes - full" will call on pension2 in cell q54
if p45 = "yes - full" will call on pension3 in cell q55
if p46 = "yes - full" will call on pension4 in cell q56

I realise I shouldn't have mentioned "formulas" at all, as they are part of the macros

Thanks Joe

If pension2, pension3 and pension4 are macros then I don't know what you mean by "calling on them in a cell" - can you explain?

And, ... what of pension1 and the cell P53 you mentioned in post #9 ????

I really would like to help, but that's hard to do if you can't make clearer exactly what you want to monitor for changes and exactly what you want to happen when a change that meets your criteria occurs.

 

[Darkshine]

If pension2, pension3 and pension4 are macros then I don't know what you mean by "calling on them in a cell" - can you explain?

And, ... what of pension1 and the cell P53 you mentioned in post #9 ????

I really would like to help, but that's hard to do if you can't make clearer exactly what you want to monitor for changes and exactly what you want to happen when a change that meets your criteria occurs.

if P53 = "Yes - Full" then macro pension1 will occur which will add the formula "=IF(P53="Yes - Full",G14,0)" in cell Q53
if P54 = "Yes - Full" then macro pension2 will occur which will add the formula "=IF(P54="Yes - Full",G17,0)" in cell Q54
if P55 = "Yes - Full" then macro pension3 will occur which will add the formula "=IF(P55="Yes - Full",G20,0)" in cell Q55
if P56 = "Yes - Full" then macro pension4 will occur which will add the formula "=IF(P56="Yes - Full",G23,0)" in cell Q56

My worksheet, when first opened, contains those 4 formulas listed above in cells Q53,54,55 & 56 respectively and "Yes - Full" is the standard answer at P53,54,55 and 56. If someone chooses something other than "Yes - Full" in P53,54,55 or 56, they will then overwrite that formula in Q53,54,55 or 56 with a value. If they subsequently change their mind and select "Yes - full" in P53,54,55 or 56, then I need the formula to return to Q53,54,55 or Q56.

That's what I'm trying to do.

I'm sorry if i'm not making sense but I do really appreciate your effort.

 

[JoeMo]

OK, now that's much clearer to me. See if this combines the two change events you want to monitor and react to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("c14,c17,c20,c23")) Is Nothing Then
    For Each c In Intersect(Target, Range("c14,c17,c20,c23"))
        If IsNumeric(c.Value) Then
            If c.Value > 1600000 Then
                MsgBox "The Allowable TSB Limit of $1,600,000.00 has been breached"
                Beep
            End If
        End If
    Next c
End If
If Not Intersect(Target, Range("P53:P56")) Is Nothing Then
    For Each c In Intersect(Target, Range("P53:P56"))
        If c.Value = "Yes - Full" Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Select Case c.Address(0, 0)
                Case "P53": Call pension1
                Case "P54": Call pension2
                Case "P55": Call pension3
                Case "P56": Call pension4
            End Select
        End If
    Next c
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If
End Sub

 

[Darkshine]

OK, now that's much clearer to me. See if this combines the two change events you want to monitor and react to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("c14,c17,c20,c23")) Is Nothing Then
    For Each c In Intersect(Target, Range("c14,c17,c20,c23"))
        If IsNumeric(c.Value) Then
            If c.Value > 1600000 Then
                MsgBox "The Allowable TSB Limit of $1,600,000.00 has been breached"
                Beep
            End If
        End If
    Next c
End If
If Not Intersect(Target, Range("P53:P56")) Is Nothing Then
    For Each c In Intersect(Target, Range("P53:P56"))
        If c.Value = "Yes - Full" Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Select Case c.Address(0, 0)
                Case "P53": Call pension1
                Case "P54": Call pension2
                Case "P55": Call pension3
                Case "P56": Call pension4
            End Select
        End If
    Next c
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If
End Sub

Perfect!

Thank you Joe

 

[JoeMo]

Perfect!

Thank you Joe

You are welcome - thanks for the reply.