Thread: unhide rows when cell is clicked

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

Click would be a poor choice given the available events and there workings.

Right click on the worksheet tab and select view code.

paste in code like this:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
if target.column = 3 Then
set rng = target.offset(1,0).Resize(5).EntireRow
rng.Hidden = not rng.hidden
cancel = true
end if
End sub

If you double click in column C then the next five rows below that row will
be hidden or unhidden based on what their current state is.

This is very basic code, so it may require some enhancement to achieve what
you really want to do.

--
Regards,
Tom Ogilvy


"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
>
>

Hi Tom,

Thanks for the response. We like the idea of the code you suggested,
however we were hoping you could suggest some ways to tweak it to
achieve the following:

A) I have 4 columns in Row 24 (C24, E24, G24, I24). My hope is that I
can put different category headings (ie, Pontiac, Chev, Honda, Toyota)
in each of these 4 cells. When I click on one of these category
headings (ie Toyota in G24) is there anyway that it can unhide the 5
rows beneath this cell, but ONLY for column G?

OR IF THIS IS NOT POSSIBLE:

B) Using the original code you gave, is there anyway that when you
hover over that cell in C the cursor can change to an arrow? We want
to indicate in some way that when you click on that cell something will
happen (right now an uninformed user would have no idea to click on
that cell, because unlike a normal link the cursor stays the same and
there is no indication that anything will happen by clicking it
(especially since you have to double click)

Thanks a lot!
Lauren

Tom Ogilvy wrote:
> Click would be a poor choice given the available events and there workings.
>
> Right click on the worksheet tab and select view code.
>
> paste in code like this:
>
> Private Sub Worksheet_BeforeDoubleClick( _
> ByVal Target As Range, Cancel As Boolean)
> if target.column = 3 Then
> set rng = target.offset(1,0).Resize(5).EntireRow
> rng.Hidden = not rng.hidden
> cancel = true
> end if
> End sub
>
> If you double click in column C then the next five rows below that row will
> be hidden or unhidden based on what their current state is.
>
> This is very basic code, so it may require some enhancement to achieve what
> you really want to do.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "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
> >
> >

No, entire rows are hidden or they are not.



Possibly what you want is to use data => Validation (validation under the
data menu).

Select I25 (cell below I24, Toyota) and select Data=>Validation, then

Select the list option, then in the resulting source box type in your values
as shown (no quotes or double quotes).

toyota1,toyota2,toyota3,toyota4,toyota5

then if you select that cell, a dropdown arrow will appear with those
choices. When you select one, that will be entered in the cell that has the
data validation.

If you want to then provide additional information for that choice, you
could have a table of information on another sheet and use a vlookup function
to fetch it.

Assume I25 has the data validation

=if(I25="",Vlookup(I25,Data!A1:F20,4,False))
the 2 says the fourth column in the Range A1:F20 for the row that contains
the value in I25. In this case, that would be column D.

you would put your header in

Or you could put a database in on the sheet, selecte the database, then
select Data=>Filter=>Autofilter

you data would have to be oriented horizontally (vice the vertical
orientation you describe).

then going to the Make column/make header, you will select from the dropdown
there and select toyota for example. Now all rows but the rows containing
toyota in the make column will be hidden. Then go to Model and only models
for visible rows will be shown for that dropdown - this is all done without
any coding.

I guess it depends on what you really are trying to do.

--
Regards,
Tom Ogilvy



"lauren_roberts08" wrote:

> Hi Tom,
>
> Thanks for the response. We like the idea of the code you suggested,
> however we were hoping you could suggest some ways to tweak it to
> achieve the following:
>
> A) I have 4 columns in Row 24 (C24, E24, G24, I24). My hope is that I
> can put different category headings (ie, Pontiac, Chev, Honda, Toyota)
> in each of these 4 cells. When I click on one of these category
> headings (ie Toyota in G24) is there anyway that it can unhide the 5
> rows beneath this cell, but ONLY for column G?
>
> OR IF THIS IS NOT POSSIBLE:
>
> B) Using the original code you gave, is there anyway that when you
> hover over that cell in C the cursor can change to an arrow? We want
> to indicate in some way that when you click on that cell something will
> happen (right now an uninformed user would have no idea to click on
> that cell, because unlike a normal link the cursor stays the same and
> there is no indication that anything will happen by clicking it
> (especially since you have to double click)
>
> Thanks a lot!
> Lauren
>
> Tom Ogilvy wrote:
> > Click would be a poor choice given the available events and there workings.
> >
> > Right click on the worksheet tab and select view code.
> >
> > paste in code like this:
> >
> > Private Sub Worksheet_BeforeDoubleClick( _
> > ByVal Target As Range, Cancel As Boolean)
> > if target.column = 3 Then
> > set rng = target.offset(1,0).Resize(5).EntireRow
> > rng.Hidden = not rng.hidden
> > cancel = true
> > end if
> > End sub
> >
> > If you double click in column C then the next five rows below that row will
> > be hidden or unhidden based on what their current state is.
> >
> > This is very basic code, so it may require some enhancement to achieve what
> > you really want to do.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "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
> > >
> > >
>
>