Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
How do I double click a cell and jump to cell's referenced cell | Excel Discussion (Misc queries) |