Unhide rows when you click a cell

[lauren_roberts08]

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

 

[Guest]

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? ;-)