Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
lauren_roberts08
 
Posts: n/a
Default Unhide rows when you click a cell

Hi everyone,

Is it possible to unhide a selection of rows when you click on a cell.
For example, if I had a cell that said "Fruit", and I clicked it, could
it unhide 5 rows below that contained specifc types of fruit (ie apple,
pear, orange etc).

I am in the very beginning stages of understanding excel so any help
would be greatly appreciated (but could it also be as straightforward
as possible!! thank you!!!)

Thanks!
lauren

  #2   Report Post  
Posted to microsoft.public.excel.misc
JLatham
 
Posts: n/a
Default Unhide rows when you click a cell

You can do this with some limitations. Best way is to use code attached to
the worksheet events. There are 2 obvious candidates for use here - the
Selection Change event which fires every time you change the selection on a
worksheet. This can make for very busy routine if you click around on the
sheet a lot or start scrolling up/down left/right on a sheet using arrow
keys. But it can work and will give you a result if you just click in a
cell.

The second option would be to use the BeforeDoubleClick event which would
require you to double-click in the cell to get it to unhide the rows.
Examples of code for both possibilities is shown. The code presumes that the
cell that is going to be the trigger-cell is C3:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Target gives you the current selection's range
'this will be a busy routine
'running every time you change selection on
'a worksheet

Dim TestRange As Range
Dim IntersectTestResult As Variant

'stop Events from firing so you can work without interruption
'and without entering this multiple times
Application.EnableEvents = False

Set TestRange = Worksheets("Sheet1").Range("C3")
Set IntersectTestResult = _
Application.Intersect(TestRange, Target)

If Not (IntersectTestResult Is Nothing) Then
MsgBox "You selected cell C3 - We would unhide the rows here."
End If
're-enable events
Application.EnableEvents = True
End Sub

and here is code that would work for the double-click option:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'Target gives you the current selection's range
'if you set Cancel=True then nothing happens
'this will be a busy routine
'running every time you change selection on
'a worksheet
Dim TestRange As Range
Dim IntersectTestResult As Variant

'stop Events from firing so you can work without interruption
'and without entering this multiple times
Application.EnableEvents = False
Set TestRange = Worksheets("Sheet1").Range("C3")
Set IntersectTestResult = _
Application.Intersect(TestRange, Target)

If IntersectTestResult Is Nothing Then
MsgBox "Normally we wouldn't do anything when you aren't in C3"
Else
MsgBox "You double-clicked in cell C3 - We would unhide the rows
here."
Cancel = True ' keeps from actually selecting contents
End If
're-enable events
Application.EnableEvents = True
End Sub


P.S. - now that you have them unhidden, how ya' gonna hide'm again? ;-)

"lauren_roberts08" wrote:

Hi everyone,

Is it possible to unhide a selection of rows when you click on a cell.
For example, if I had a cell that said "Fruit", and I clicked it, could
it unhide 5 rows below that contained specifc types of fruit (ie apple,
pear, orange etc).

I am in the very beginning stages of understanding excel so any help
would be greatly appreciated (but could it also be as straightforward
as possible!! thank you!!!)

Thanks!
lauren


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
resetting last cell jagdish.eashwar Excel Discussion (Misc queries) 11 March 31st 06 02:06 AM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Protect Workbook vs Worksheet?? Dan B Excel Worksheet Functions 3 November 7th 05 10:02 PM
How do I double click a cell and jump to cell's referenced cell JerryJuice Excel Discussion (Misc queries) 2 September 10th 05 10:24 PM


All times are GMT +1. The time now is 02:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"