Excel Drilling down in excel :: MrExcel Message Board


 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

Drilling down in excel
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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.

Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows ME
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
3
Relay2201  
4
PR1500  
5
HW100  
6
SW200  
7
Consulting200  
8
PR2678  
9
TR1567  
10
TR2456  
Sheet1 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


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

Post Sun Aug 04, 2002 7:06 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


Joined: 09 Feb 2002
Posts: 8388
Location: Marion, IL
Flag: Colombia

Status: Offline

 Reply with quote  

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

_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Post Mon Aug 05, 2002 12:01 am 
 View user's profile Send private message Visit poster's website

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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.

Post Mon Aug 05, 2002 1:45 am 
 View user's profile Send private message

Tom Urtis
MrExcel MVP


Joined: 11 Feb 2002
Posts: 2034
Location: San Francisco / Silicon Valley, California USA
Flag: Blank

Status: Offline

 Reply with quote  

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?

_________________
Tom Urtis

Post Mon Aug 05, 2002 5:43 am 
 View user's profile Send private message Send e-mail Visit poster's website

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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

Post Mon Aug 05, 2002 7:23 am 
 View user's profile Send private message

Tom Urtis
MrExcel MVP


Joined: 11 Feb 2002
Posts: 2034
Location: San Francisco / Silicon Valley, California USA
Flag: Blank

Status: Offline

 Reply with quote  

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

_________________
Tom Urtis

Post Mon Aug 05, 2002 1:12 pm 
 View user's profile Send private message Send e-mail Visit poster's website

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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

Post Mon Aug 05, 2002 1:50 pm 
 View user's profile Send private message

Tom Urtis
MrExcel MVP


Joined: 11 Feb 2002
Posts: 2034
Location: San Francisco / Silicon Valley, California USA
Flag: Blank

Status: Offline

 Reply with quote  

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?

_________________
Tom Urtis

Post Mon Aug 05, 2002 2:00 pm 
 View user's profile Send private message Send e-mail Visit poster's website

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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

Thanks so much.....

Post Mon Aug 05, 2002 2:48 pm 
 View user's profile Send private message

baggarwal
Board Master


Joined: 11 Jul 2002
Posts: 290


Status: Offline

 Reply with quote  

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

Post Mon Aug 05, 2002 3:19 pm 
 View user's profile Send private message

Tom Urtis
MrExcel MVP


Joined: 11 Feb 2002
Posts: 2034
Location: San Francisco / Silicon Valley, California USA
Flag: Blank

Status: Offline

 Reply with quote  

You asked for 2 scenarios so here they are, one for B3 and one for B4.

Be careful with this, it literally does what you want, which is to only show a small visible range, but in so doing it hides about 16,777,210 other cells, albeit clumped in column abd row ranges, but still, depending on your system resources, can have an effect on memory. Excel is a 2-dimensional program (height in rows and width in columns), so the sequence of the code is to unhide everything, then selectively re-hide what you don't want to see.

It would be better to limit the scroll area to where you want your user to go when they have drilled down, instead of all this hiding. or, maybe dialog boxes or user forms would be more efficient to show the drilled down information. All options are your decision...here's what you literally requested:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
'To show only A102:B105
Case "$B$3"
If Target = "" Then
Cancel = True
Else
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
Range("C:IV").EntireColumn.Hidden = True
Range("1:101,106:65536").EntireRow.Hidden = True
Application.ScreenUpdating = True
Cancel = True
End If
'To show only A102:A105 and C102:C105
Case "$B$4"
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
Range("B:B,D:IV").EntireColumn.Hidden = True
Range("1:101,106:65536").EntireRow.Hidden = True
Application.ScreenUpdating = True
Cancel = True
Case Else
Exit Sub
End Select
End Sub


_________________
Tom Urtis

Post Mon Aug 05, 2002 4:39 pm 
 View user's profile Send private message Send e-mail Visit poster's website
  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.