Run code only when cell says "Yes"

[Submersed]

Hi,

I have some code that needs to run when the cell says "Yes"..here is the code

If Sheets("Order2").Cells(67, 10) = "Yes" Then
Reply = MsgBox("Is this included in the Price?", vbYesNo)
If Reply = vbNo Then
Sheets("Order2").Cells(17, 9) = Sheets("Order2").Cells(17, 14)
Sheets("Order2").Cells(17, 11) = Sheets("Order2").Cells(17, 14)
End If
End If

 

[Lewiy]

Umm, can't see what you need. You already have the If statement checking that the cell says "Yes". What more are you after?

 

[HalfAce]

I have some code that needs to run when the cell says "Yes"

I agree. If by 'the cell' you mean Sheet 'Order2' cell J67, then it's already working as it's
supposed to.
Unless you mean making the entry of "Yes" into J67 being what executes the code, can you
elaborate a bit on what you're after?

 

[Jonmo1]

Unless you mean making the entry of "Yes" into J67 being what executes the code

Agreed, I think that's exactly what the OP meant.

Is J67 changed by manual user entry, or via formula?

 

[Jonmo1]

right click the Sheet "Order 2" Name Tab, View Code

Paste 1 (and only 1) of the following 2 codes...

IF J17 is changed VIA Manual User Entry, use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$17" AND Target.Value = "Yes" Then
    If MsgBox("Is this included in the Price?", vbYesNo) = vbNo Then
        Cells(17, 9) = Cells(17, 14)
        Cells(17, 11) = Cells(17, 14)
    End If
End Sub

If J17 is changed VIA Formula, use this code..

Private Sub Worksheet_Calculate()
If Cells(67, 10) = "Yes" Then
    If MsgBox("Is this included in the Price?", vbYesNo) = vbNo Then
        Cells(17, 9) = Cells(17, 14)
        Cells(17, 11) = Cells(17, 14)
    End If
End If
End Sub

 

[Submersed]

yea..that is what i wanted but....what can i put as the private sub? because i have had it on worksheet_change and if the cell J67 says Yes it comes up everytime i select "Order2" sheet.

So how do i get it to activate only when the user puts "Yes" in that cell.

I have also tried Private Sub Worksheet_SelectionChange and that comes up every time I change cell.

 

[Submersed]

right click the Sheet "Order 2" Name Tab, View Code

Paste 1 (and only 1) of the following 2 codes...

IF J17 is changed VIA Manual User Entry, use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$17" AND Target.Value = "Yes" Then
    If MsgBox("Is this included in the Price?", vbYesNo) = vbNo Then
        Cells(17, 9) = Cells(17, 14)
        Cells(17, 11) = Cells(17, 14)
    End If
End Sub

If J17 is changed VIA Formula, use this code..

Private Sub Worksheet_Calculate()
If Cells(67, 10) = "Yes" Then
    If MsgBox("Is this included in the Price?", vbYesNo) = vbNo Then
        Cells(17, 9) = Cells(17, 14)
        Cells(17, 11) = Cells(17, 14)
    End If
End If
End Sub

That top code works but i have a validation on the cell...if it says "No" i need it not to come up with the msgbox, only come up if it says "Yes"

 

[Lewiy]

Simply activating a sheet should not trigger the worksheet change event unless you have something in the worksheet activate which is editing the target cell.

 

[Submersed]

ok thanks alot people...just managed to sort it....thanks to you lot!!

heres the code i needed..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$67" Then
If Sheets("Order2").cells(67, 10) = "Yes" Then
    If MsgBox("Is this included in the Price?", vbYesNo) = vbNo Then
        cells(17, 9) = cells(17, 14)
        cells(17, 11) = cells(17, 14)
    End If
    End If
    End If
End Sub

It works perfectly.... Many Thanks