Drilling down in excel

[baggarwal]

Hi All:

In the spreadsheet attached, what I want to do is setup a drilldown so that if someone clicks a cell with a value in it, it will show everything that makes up that number.

Book1
	A	B	C	D
3	Relay	2201
4	PR1	500
5	HW	100
6	SW	200
7	Consulting	200
8	PR2	678
9	TR1	567
10	TR2	456
Sheet1

So in the example attached. I want to setup the spreadsheet so that if someone double clicks cell B3, which is $2,201 then everything that makes it up...i.e cells B4, B8, B9 and B10 are shown otherwise hidden. If someone wants to double click B4 then I want cell cells A5 to B7 to show up.

Can someone please help work through this.

Thanks,

BA

 

[Juan Pablo González]

Have thought/seen Group and Outline ? this seems like a good solution for your problem

 

[baggarwal]

Group and Outline is something I use all the time but it won't work in this case because I want the detail to show after someone double clicks the number.

Is there a way to show the detail if someone double clicks the number.

 

[Tom Urtis]

I'm not sure if you are asking how to get the drilldown itself to occur (and hence the drilldown information accessed), or how to write the code that produces your drilldown information.

To offer a drilldown feature, maybe doubleclicking a cell is a good way to set it in motion. So, put a double click event in your sheet code, like this (I took column B because it's the one with numbers on it per your post):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 And Target.Value <> "" Then

'Your code goes here that speaks to the number value in the Target that was double clicked

Cancel = True

Else
Cancel = True

End If
End Sub

Does this help at all or did I guess wrong on what you want?

 

[baggarwal]

Hi Tom:

I am looking to get an idea of the VBA code that would enable drilldown. When I tried your code though I got a compile error. Can you please show me how to write the code that would lets say do the following:

If someone doubleclicks cell B3, then A4:B4, A8 to B10 will be shown. What I also want to do is keep these details hidden initially until someone actually double clicks the number.

Thanks so much for your help.

Bharat

 

[Tom Urtis]

I tested that code and it works fine. Did you put it in the sheet module?

Try this modification to get you started. Right click on your sheet tab, left click on View Code, and paste this into the module.

When you double click on B3 or B4, a message box will pop up that represents the location where you'd place whatever code you need to compose to produce the information about A4 and A8:A10 respectively.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$B$3"
MsgBox "Insert code here for cell A4."
Cancel = True
Case "$B$4"
MsgBox "Insert code here for range A8:D10."
Cancel = True
Case Else
Cancel = True
End Select
End Sub

 

[baggarwal]

Hi Tom:

I inserted the code where it was indicated but it still isn't doing what I need it to.

Can you please help me fix this code:
rivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$B$3"
If Target.Address = "$B$3" Then
If Target = "" Then
bShowHide = False
End If
Selection.EntireRow(4).Hidden = False
End If
Cancel = True
Case "$B$4"
MsgBox "Insert code here for range A8:D10."
Cancel = True
Case Else
Cancel = True
End Select
End Sub

It won't unhide the fourth row for me. One addition to this I need though is I need it to hide rows 4,8,9 but I need to keep these rows hidden initially.

Thanks again for your great support Tom,

Bharat

 

[Tom Urtis]

I'm still not sure of what your are trying to achieve when you double click B3, but try this modification of your code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$B$3"
If Target = "" Then
Cancel = True
Else
Range("2:10").EntireRow.Hidden = True
Range("4:4").EntireRow.Hidden = False
Cancel = True
End If
Case "$B$4"
MsgBox "Insert code here for range A8:D10."
Cancel = True
Case Else
Exit Sub
End Select
End Sub

You had a few unnecessary lines in yours. This will hide rows 2:10 and unhide (only show as visible) row 4.

Does this get you closer to what you want?

 

[baggarwal]

Tom, you are so awesome this get me really close.

Thanks so much.....

 

[baggarwal]

Hi Tom:

What code would I use to unhide only a block of cells. So in the code you provided me you showed me how to unhide an entire row: so for example to hide row 4 you use the following code:
Range("4:4").EntireRow.Hidden = False

What if I wanted to unhide a block of cells only so for example A102:B105. How would I do that?

Lets say I wanted to unhide only A102:A105 and C102:C105.

Thanks again for all of your help Tom,

Bharat