Macro Help

[Jeremy]

I have recorded a macro to hid row 10. How would I automate this macro to
hide if E2 has yes and unhide if it has no?


Thank you

Jeremy

 

[Jacob Skaria]

Sub Mac()
Rows(10).Hidden = IIf(Range("E2") = "Yes", True, False)
End Sub

If this post helps click Yes

 

[Jeremy]

Can you help me with where to put this. Thank you for your response.

 

[Jeremy]

I have it working but you have to play the macro. How would you get the
macro to auto play?

 

[Don Guillett]

Right click sheet tab>view code>insert this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("e2").Address Then Exit Sub
If LCase(Target) = "yes" Then
Rows(10).Hidden = True
Else
Rows(10).Hidden = False
End If
End Sub

 

[Jacob Skaria]

Right click the sheet tab>View code and paste the below code and try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("E2")) Is Nothing Then
Rows(10).Hidden = IIf(Range("E2") = "Yes", True, False)
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Don Guillett]

I had forgotten about I if. Minor tweaks
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False' not necessary
If Not Application.Intersect(Target, Range("E2")) Is Nothing Then
Rows(10).Hidden = IIf(UCase(Range("E2")) = "YES", True, False)'spelling
End If
'Application.EnableEvents = True
End Sub

 

[Jeremy]

Do I place this in the macro when I edit it?

I had forgotten about I if. Minor tweaks
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False' not necessary
If Not Application.Intersect(Target, Range("E2")) Is Nothing Then
Rows(10).Hidden = IIf(UCase(Range("E2")) = "YES", True, False)'spelling
End If
'Application.EnableEvents = True
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

If you always use Yes you can leave as is. But, if YES or yes or yEs then
change the one line.
Or, use mine instead.

 

[Jacob Skaria]

Dont forget to set the Security level to low/medium in (Tools|Macro|Security)..

If this post helps click Yes