I need to call a macro when a cell is = Yes

[Jay3]

Hi Y'all,

Can someone send me some code to enable me to call a procedure when yes is selected from a drop down list?

Basically I have a spreadsheet which people can record tasks for me to complete. I have a column entitled Notify where users can select Yes / No.

Is they select Yes I want it to activate another macro.....any ideas?

Thanks,
Jay3

 

[Ruddles]

Paste this into your worksheet's code module:-

Option Explicit
 
Private Sub Worksheet_Calculate()
  
  Const CalcCell As String = "A4"
  Const LastValue As String = "A5"
  
  If Range(CalcCell).Value <> Range(LastValue).Value Then
    Range(LastValue) = Range(CalcCell).Value
    ' call SomeOtherMacro
  End If
 
End Sub

A4 and A5 are the addesses of two spare cells on your worksheet. CalcCell must be set up to contain a reference to the linked cell of the drop-down list, so if the linked cell is X1, cell A4 should contain =X1.

 

[Jay3]

Thanks for this, however, I'm not sure how it works and the macro doesn't seem to run when I select yes from the drop down menu in spreadsheet.

Can anyone help and is there an easier way to call another macro when "Yes" is selected from a data validation list?

Thanks,
Jay3

 

[Ruddles]

Where I put ' call SomeOtherMacro, you need to check the value in the linked cell and call your macro. So if your linked cell is X99 and "Yes" is the first item in the list, you'd do:-

If Range("X99").Value = 1 Then Call SomeOtherMacro

or:-

If Range("X99").Value = "Yes" Then Call SomeOtherMacro

Note that the value of the linked cell may be the position of your selection in the list or it may be the selected value depending on whether you're using a form control or an ActiveX control.

If you place a breakpoint at the start of the Worksheet_Calculate macro and then select something from your drop-down, you can step through it using F8 and see how it's working. It will only be triggered when the value of the drop-down actually changes though.

 

[Jay3]

Hi again,

Thanks for your help on this it's much appreciated. I still can't get it to work though....is there anyway I can email you my workbook to show you what I'm trying to do?

 

[Ruddles]

Check your private messages.

 

[hawhy]

Re: I need to call a macro when a cell is = Yes and a different macro if the cell is no

Hi Guys,

I've looked at so many forums and cant find a simple solution to this and i know there must be...

I have 1 cell with a drop down ("yes/no") say cell A1

If I choose "yes" in A1 i would like a macro to run, If i choose "no" i would like a different macro to run.

Thanks guys!

 

[Ruddles]

This goes in the code module for the worksheet - not ThisWorkbook and not a new module:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1").Value = "Yes" Then Call Macro1
    If Range("A1").Value = "No" Then Call Macro2
  End If
End Sub
 
[COLOR=red]Private Sub Macro1(): MsgBox "You clicked 'Yes'": End Sub[/COLOR]
[COLOR=red][/COLOR] 
[COLOR=red]Private Sub Macro2(): MsgBox "You clicked 'No'": End Sub[/COLOR]

The red bits are just there for testing. Shout if it doesn't work.

 

[hawhy]

Ruddles,

Thank you for this, i know it should work but for the life of me, i cannot seem to make it work!

Any chance i can mail you my file so you what im doing wrong?

Appreciate your input (again)!

Thank you

 

[Ruddles]

I'll PM you.