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!

 

[jtakw]

Hi,

Try putting your code in Worksheet_Change event rather than Worksheet_SelectionChange.

 

[AlphaFrog]

Have the code change or clear the cell value.

[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]
[COLOR=#333333]If Target.Value = "Yes - Full" Then[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Call pension1
[/COLOR][B]Target.Value = ""[/B]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Darkshine]

Thanks for your reply.

Now you're going to think I'm stupid.

I originally had my code in there but I have another private sub in there and the two wouldn't work together.

I was getting an ambiguous name error.

which part of the code would I need to change for that error not to occur?

Thanks.

 

[Darkshine]

Have the code change or clear the cell value.

[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]
[COLOR=#333333]If Target.Value = "Yes - Full" Then[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Call pension1
[/COLOR][B]Target.Value = ""[/B]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

I need the value to remain, so that doesn't work for me, sorry.

 

[JoeMo]

Show us your other worksheet_change event code and someone can help you combine it with the one you posted which needs to be revised to a worksheet_change event.

 

[Darkshine]

Show us your other worksheet_change event code and someone can help you combine it with the one you posted which needs to be revised to a worksheet_change event.

Private Sub Worksheet_Change(ByVal t As Range)
If Not Intersect(t, Range("c14,c17,c20,c23")) Is Nothing Then
If t.Value > 1600000 Then
MsgBox "The Allowable TSB Limit of $1,600,000.00 has been breached", beepnow()
End If
End If
End Sub

thanks for your help on this

 

[JoeMo]

Private Sub Worksheet_Change(ByVal t As Range)
If Not Intersect(t, Range("c14,c17,c20,c23")) Is Nothing Then
If t.Value > 1600000 Then
MsgBox "The Allowable TSB Limit of $1,600,000.00 has been breached", beepnow()
End If
End If
End Sub

thanks for your help on this

What range do you want to monitor for the "Yes - Full" change event?

 

[Darkshine]

what range do you want to monitor for the "yes - full" change event?

p53

 

[Darkshine]

p53

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?