Hiding rows based on cell contents

[andrewb90]

I need to get a formula or a piece of code that looks at cell D3 and if the number is not 5 then rows 800 & 810 will be hidden.

Can anybody help with this?

Many Thanks,

Andrew

 

[Rick Rothstein]

Right click the name tab at the bottom of the worksheet that you want to have this functionality and select "View Code" from the popup menu that appears, then copy/paste the following code into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D5")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D5") <> 5
  End If
End Sub

 

[andrewb90]

When I change the data in my key cell I get an error:
Compile Error:
Ambiguous name detected: Worksheet_Change

 

[Marcelo Branco]

Hi Rick,

I think there is a typo in your post.

Instead of Range("D5") the OP says:
"..a piece of code that looks at cell D3 and if the number is not 5.."

So i think the correct is

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("[B]D3[/B]")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("[B]D3[/B]") <> 5
  End If
End Sub

M.

 

[Rick Rothstein]

That error message means you have to Change event procedures in the sheet module (the code window that opened when you followed my procedure). There can only be one Change event procedure in a sheet module. If the other Change event is your first attempt to do what my code does, delete it. If there is other code in that procedure doing other things, then you will need to post its code so we can integrate the code from both procedures into a single event procedure for you.

EDIT NOTE: Andrew caught a typo (thanks Andrew) that I made in my code. It code you should use is this...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
  End If
End Sub

 

[andrewb90]

I also have this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M3" Then Me.Shapes("Shape51").Visible = Target.Value >= 5
End Sub

 

[Rick Rothstein]

Try replacing both of your current Change event procedures with this single one...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
  End If
  If Not Intersect(Target, Range("M3")) Is Nothing Then
    Me.Shapes("Shape51").Visible = Target.Value >= 5
  End If
End Sub

 

[andrewb90]

rows 800 & 810 don't seem to hide when a number other than 5 is entered...
Any ideas?

 

[Rick Rothstein]

It does for me. You did install the code in a worksheet module, not a standard module, right? Standard modules are where macros go, but that is not where evernt code goes. In case some of this is new to you, the easiest way to get to the correct worksheet module is to right click the name tab at the bottom of the worksheet that you want to have this functionality, then click "View Code" on the popup menu that appears. This will open the worksheet module's code window where you are to copy/paste the code at. Speaking of code, I have a minor tweak for the code I posted earlier; use this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("D3")) Is Nothing Then
Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
End If
If Not Intersect(Target, Range("M3")) Is Nothing Then
Me.Shapes("Shape51").Visible = Range("M3") >= 5
End If
End Sub

 

[andrewb90]

Awesome! thanks for all of your help!

By the way, would you happen to know how to have a macro button open up a gmail and compose a message?