Macro Help

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

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
 
Sub Mac()
Rows(10).Hidden = IIf(Range("E2") = "Yes", True, False)
End Sub

If this post helps click Yes
 
I have it working but you have to play the macro. How would you get the
macro to auto play?
 
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
 
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
 
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
 
Do I place this in the macro when I edit it?

Don Guillett said:
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)
Click to expand...
 
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.
 
Dont forget to set the Security level to low/medium in (Tools|Macro|Security)..

If this post helps click Yes
 
Back
Top