Run code only when cell says "Yes"

Submersed

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
  1. 365
Platform
  1. Windows
Hi,

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

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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Umm, can't see what you need. You already have the If statement checking that the cell says "Yes". What more are you after?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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

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..

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
 
Upvote 0
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.
 
Upvote 0
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

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..

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"
 
Upvote 0
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.
 
Upvote 0
ok thanks alot people...just managed to sort it....thanks to you lot!! :)

heres the code i needed..

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top