Show/Hide Row Range Based on Cell Value

[twothings]

Hello all and thank you for your assistance.

I have searched forums thoroughly for a solution to my problem without success so seek your expertise for an answer.

Situation
I have a list box with the following 13 options:

[TABLE="width: 94"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Show All[/TD]
[/TR]
[TR]
[TD]July 14[/TD]
[/TR]
[TR]
[TD]August 14[/TD]
[/TR]
[TR]
[TD]September 14[/TD]
[/TR]
[TR]
[TD]October 14[/TD]
[/TR]
[TR]
[TD]November 14[/TD]
[/TR]
[TR]
[TD]December 14[/TD]
[/TR]
[TR]
[TD]January 15[/TD]
[/TR]
[TR]
[TD]February 15[/TD]
[/TR]
[TR]
[TD]March 15[/TD]
[/TR]
[TR]
[TD]April 15[/TD]
[/TR]
[TR]
[TD]May 15[/TD]
[/TR]
[TR]
[TD]June 15[/TD]
[/TR]
</tbody>[/TABLE]

Depending on what is selected, cell C2 gets a number between 1 and 13.

In row ranges B8:B372, I have the following date ranges for a local financial year:

[TABLE="width: 98"]
<colgroup><col></colgroup><tbody>[TR]
[TD]01/07/14[/TD]
[/TR]
[TR]
[TD]02/07/14
03/07/14
04/07/14
05/07/14
...
30/06/15[/TD]
[/TR]
</tbody>[/TABLE]


Problem
What I need to occur is the following:

If C2 = 1, all row dates show
If C2 = 2, only show July 14 dates (1/7/14-31/7/14). All other rows in range B8:B372 hide.
If C2 = 3, only show August 14 dates, others in range hide
If C2 = 4, only show Sept 14 dates, others in range hide
If C2 = 5, only show Oct 14 dates, others in range hide
...
If C2 = 13, only show June 15 dates, others in range.


Thank you for taking the time to read this and provide assistance.

Scott

 

[Teeroy]

Hi Scott,

And here is where we swear at M$. You are using a Form Control List Box, not an ActiveX ListBox (note the lack of a space in the name). Form Controls and ActiveX controls are different animals with the same (or similar) names. The form controls do not expose events to the worksheet in the same way as ActiveX controls do. If you delete the Form Control List Box and insert an ActiveX ListBox you should be on the right path.

 

[twothings]

Thanks for that. I have implemented the new ActiveX ListBox and have it referencing cell B2 which is set to number format. Since the ListBox cell range is a date in custom format, ie. mmmm yy, the resulting linked cell result is a date in number format. ie. selecting August 14 in the listbox, results in cell B2 displaying 41852. Without becoming very complicated with cell formulas, is it possible in VBA to have the linked cell value pulled from elsewhere?

So in short, when clicking on:

Show All, cell B2 will result in 1 (instead of Show All),
July 14, cell B2 will result in 2 (instead of 41821), etc and so on.

[TABLE="width: 158"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Show All[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]July 14[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]August 14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]September 14[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]October 14[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]November 14[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]December 14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]January 15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]February 15[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]March 15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]April 15[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]May 15[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]June 15[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]


My current file is available here:

http://s000.tinyupload.com/?file_id=09932552625885224089

Thanks!

 

[twothings]

Disregard above, I have found a quick solution using a long IF statement. All working great. Thank you to those members that provided me assistance!

 

[Teeroy]

For your info the fix for the ListBox is simple. Where you have the ListBoxes Listfill range directed to a single column, change it to two columns and have 1-13 in the second column (adjacent the dates) and then set the ListBoxes BoundColumn property to 2. This way your LinkedCell gets the value from the offset column, rather than the selection.