Creating Customize Reports

[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 series of
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

 

[Dave Peterson]

I put 6 checkboxes from the Forms toolbar (not control toolbox toolbar) on
Sheet1 in my test workbook.

When I added them, I noticed that excel assigned the names:
Check Box 1
Check Box 2
....
Check Box 6

(in the namebox (to the left of the formula bar)).

(Change the caption to something meaningful, though--Like "Check this to hide
Section ##.")

Then I went to my other sheet and defined 6 named ranges.
Section1
Section2
....
Section6

These names point at the range that is associated with each checkbox.

Then I rightclicked on this worksheet with the named ranges and selected View
Code.

I pasted this in:

Option Explicit
Private Sub Worksheet_Activate()

Dim iCtr As Long
For iCtr = 1 To Worksheets("sheet1").CheckBoxes.Count
Me.Range("Section" & iCtr).EntireRow.Hidden _
= CBool(Worksheets("sheet1") _
.CheckBoxes("check box " & iCtr).Value = xlOn)
Next iCtr

End Sub

then back to sheet1 and check a few boxes.

When I click on the sheet with the Sections (and with the code), rows will be
hidden accordingly.

If it makes more sense, change xlOn to xlOff. (That depends on how you word the
captions.)

 

[RickGreg]

Thanks Dave. Works perfectly.

 

[RickGreg]

Dave-

Your code worked perfectly... EXCEPT... Now that I have put the finishing
touches on the workbook, I want to protect the sheets to prevent users from
altering content that shouldn't be changed.

When I do this, it prevents your HIDE macro from working. Is there code I
could insert before/after the hide/unhide macro that could temporarily
unprotect, then re-protect a sheet?

Thanks again.

-Rick

 

[Dave Peterson]

If you record a macro when you unprotect a sheet, you'll get most of the code.

You should end up with something like:

Option Explicit
Private Sub Worksheet_Activate()

Dim iCtr As Long

me.unprotect password:="hi there"

For iCtr = 1 To Worksheets("sheet1").CheckBoxes.Count
Me.Range("Section" & iCtr).EntireRow.Hidden _
= CBool(Worksheets("sheet1") _
.CheckBoxes("check box " & iCtr).Value = xlOn)
Next iCtr

me.protect password:="hi there"

End Sub