Why is this simple "On Change" Macro not working?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
very confused,
I thought id done this right but its not working

All i want is for when i select either Yes or No from a drop down box to run one or the other macros?

this is what i wrote, can someone please tel me where i went wrong?

thanks

Tony



Code:
Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$y$26" Then
If Range("Y26:Y27").Value = "Yes" Then
Call zzRedBoxhide
If Range("Y26:Y27").Value = "No" Then
Call zzRedBoxunhide


End If


End If
End If
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,015
Test Y26 and Y27 separately.....you need to distinguish between requiring one cell to have the value "Yes" and requiring two cells to have that value.
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
made a few tinyadjustments thanks for the advice,
now it runs if the cell = Yes but nothing if it no?
have i missed something here?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$Y$26" Then
If Range("Y26").Value = "Yes" Then
Call zzRedBoxhide
If Range("Y26").Value = "No" Then
Call zzRedBoxunhide


End If


End If
End If
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,984
If you indented, it would be easier to see that the If "No" test is inside the If "Yes" test.

Try using an ElseIF construction

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Y$26" Then
        If Range("Y26").Value = "Yes" Then
            Call zzRedBoxhide
        ElseIf Range("Y26").Value = "No" Then
            Call zzRedBoxunhide
        End If
    End If
End Sub
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Mikerickson, very good advice, works pefectly,
Thank you very much

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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