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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Submersed

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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"
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,606
Messages
5,930,869
Members
436,764
Latest member
avalladarez

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
Top