Hiding Rows Based on Data Input, or Not :: MrExcel Message Board
With Macromedia Shockwave Player, you can enjoy multimedia games, learning applications, and product demonstrations on the Web, using exciting new 3D technology. Get it at: http://www.macromedia.com/shockwave/download



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Hiding Rows Based on Data Input, or Not
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Fujirich
Board Regular


Joined: 01 May 2003
Posts: 86
Location: Charlotte, NC
Flag: Usa

Status: Offline

 Reply with quote  

Hiding Rows Based on Data Input, or Not

Okay, from the outset, let me say that I am more or less an Excel VBA boob - I barely know enough about it to even get into trouble. But I do have a question about a function I'd like to include in my next project that I'm sure I can only do with VBA.

I'd like to hide or reveal rows on one sheet based on data being entered (or not) in a cell on another sheet.

In doing so, I might set up a hidden column on my data entry sheet that returns a "True" or "False" based on entries being added or selected from specific cells. I want to trigger certain rows on another sheet to be visible or hidden based on the "True" or "False" result.

For the purpose of this example, let's use the following:

Sheet 1 named: Data Entry
Sheet 2 named: Results

Cell A5 on the Data Entry sheet may (or may not) have a value entered (or a choice made from a list). On the same sheet, cell K5 returns "True" if the user did anything in cell A5, and "False" if nothing is done.

Based on a "True" result in K5 on the Data Entry sheet, I'd like to show row 12 on the Results sheet, or keep it hidden if the result of K5 is "False".

That's it. My thanks to any and all who can offer guidance on this.

Thanks,
Rich

Post Sat Sep 20, 2003 7:46 pm 
 View user's profile Send private message

masim
Board Regular


Joined: 19 Jun 2003
Posts: 56
Location: Pakistan
Flag: Pakistan

Status: Offline

 Reply with quote  

Re: Hiding Rows Based on Data Input, or Not

Hi

May be this works for you....

Right click on sheet tab data entry and then choose viewcode.

when VB editor opens paste the following.



Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Range("K5").Value) = "TRUE" Then

Sheets("results").Range("A12").EntireRow.Hidden = False
Else

Sheets("results").Range("A12").EntireRow.Hidden = True

End If

End Sub

hope this works ...
Regards
Asim

Post Sat Sep 20, 2003 8:28 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Hiding Rows Based on Data Input, or Not

It seems a bit limited as it will only work for the cells you mentioned, but maybe we can expand on it later.

In cell K5 i have =IF(A5<>"","TRUE","FALSE")

right click on the Data Entry tab and view code. Delete any thing there and enter the macro below

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Range("K5").Value = "FALSE" Then

With Worksheets("Results").Rows("12:12")
.EntireRow.Hidden = True
If Worksheets("Data Entry").Range("K5").Value = "TRUE" Then
.EntireRow.Hidden = False

End If
End With
End If
End Sub

Now anytime you make a change in the Data Entry Sheet it will be checked and the Results sheet amended as required.
_________________
George J

Post Sat Sep 20, 2003 8:30 pm 
 View user's profile Send private message Send e-mail

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Hiding Rows Based on Data Input, or Not

Hi Rich:

Try the following Worksheet_Change code in the 'Data Entry' sheet

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If ['Data Entry'!K5] = True Then
        Sheets("Results").Rows("12:12").EntireRow.Hidden = True
        Else
            Sheets("Results").Rows("12:12").EntireRow.Hidden = False
    End If
End Sub



Does it help? If I have misunderstood your question -- my apologies!
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 8:44 pm 
 View user's profile Send private message

Fujirich
Board Regular


Joined: 01 May 2003
Posts: 86
Location: Charlotte, NC
Flag: Usa

Status: Offline

 Reply with quote  

Re: Hiding Rows Based on Data Input, or Not

Masim, George, & Yogi:

Thanks so much guys! I was able to get Masim's and Yogi's solutions to work as hoped, but for some reason, couldn't get George's code to make the row hide as I wanted. I'm sure it's something I'm doing George - as I said, I'm a Excel VBA novice.

Thanks guys for making this a place that rookies can come, ask questions without feeling overwhelmed or belittled. Very appreciated!

Rich

Post Sat Sep 20, 2003 11:06 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.