How to selectively exclude content

[RickGreg]

I'm hoping someone has an idea on how to accomplish what I'm trying to do:

I have an excel workbook with multiple sheets. One sheet includes input
fields for a series of topics, the next contains a series of calculations
driven by the input values on the previous sheet. The third sheet contains
a neat summary report for printing, which contains the bottom line results
for each main calculation on the previous page (about 10 major areas being
reported on).

My client has now requested the following: Can we create a menu or radio
buttons on the first page that allows users to select which of the
calculations to include in the report? For example, if they want to run an
analysis to include only content areas 1, 3, 7 and 9, how might I set up the
Report page so it pulls those results, and not all the others. I could make
them invisible with simple conditional formatting, but it would seem that
would leave gaps where results 2,4,5,6 etc would normally be.

If you need more info, please ask.

Many thanks, Rick

 

[J Laroche]

Rick, put the following code in a VBA module, repeating (and modifying) for
each area of the report.

Sub Area1_Control()
Range("area1").Rows.Hidden = Not Range("area1_link").Value
End Sub

Sub Area2_Control()
Range("area2").Rows.Hidden = Not Range("area2_link").Value
End Sub

etc...

After you put the checkboxes (not radio buttons) on sheet 1 define linked
cells for them (control-click on the checkbox, select Format Control in the
pop-up, then go to the Control tab). Here I named the first linked cell
area1_link. I wish we could dispense with it by reading the checkboxes
directly in the code, but my attempts were fruitless. Maybe our experts on
this newsgroup can explain why or find a solution.

You must also link the checkboxes with their code. Control-click again on
each checkbox, select Assign Macro in the pop-up, then select the
appropriate macro.

Finally define the report areas and give them a name, like area1 and area2
here. The report areas can be entire rows or only contain a limited number
of columns.

JL
Mac OS X 10.3.7, Office v.X 10.1.6


RickGreg wrote on 2005/01/27 15:16:

 

[RickGreg]

JL- Thanks for the help. I followed your instructions in a test file and
ran into a problem. When I select Format Control, go to the Control tab and
enter "area1_link" in the Cell Link box, I get a message "Reference is Not
Valid"

Am I missing something?

Thx.

-Rick

 

[J Laroche]

Yes, you didn't name the range first. There are two basic ways to approach
this.
1- Name the cells first, then type the name in the Cell Link textbox.
2- When the cursor is flashing in the Cell Link textbox, click on the linked
cell on the worksheet to enter its A1 coordinates (you can move the dialog
box if it's in the way, or click on the pyramid next to the textbox to
minimize the dialog). Then name the cells later.

I've assumed so far that you know how to name a range. If not, here's the
quick setup. Select one or many cells at once, or entire rows or entire
columns, then click in the Name Box (where the active cell's coordinates
usually appears, in the Formula bar) and type the name.

Jean


RickGreg wrote on 2005/01/28 08:56: