+ Reply to Thread
Results 1 to 5 of 5

Creating Customize Reports

  1. #1
    RickGreg
    Guest

    Creating Customize Reports

    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


  2. #2
    Dave Peterson
    Guest

    Re: Creating Customize Reports

    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 wrote:
    >
    > 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

  3. #3
    RickGreg
    Guest

    Re: Creating Customize Reports

    Thanks Dave. Works perfectly.

    > From: Dave Peterson <[email protected]>
    > Reply-To: [email protected]
    > Newsgroups: microsoft.public.excel.misc
    > Date: Thu, 27 Jan 2005 18:21:32 -0600
    > Subject: Re: Creating Customize Reports
    >
    > 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



  4. #4
    RickGreg
    Guest

    Re: Creating Customize Reports

    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

    > From: Dave Peterson <[email protected]>
    > Reply-To: [email protected]
    > Newsgroups: microsoft.public.excel.misc
    > Date: Thu, 27 Jan 2005 18:21:32 -0600
    > Subject: Re: Creating Customize Reports
    >
    > 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 wrote:
    >>
    >> 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



  5. #5
    Dave Peterson
    Guest

    Re: Creating Customize Reports

    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


    RickGreg wrote:
    >
    > 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
    >
    > > From: Dave Peterson <[email protected]>
    > > Reply-To: [email protected]
    > > Newsgroups: microsoft.public.excel.misc
    > > Date: Thu, 27 Jan 2005 18:21:32 -0600
    > > Subject: Re: Creating Customize Reports
    > >
    > > 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 wrote:
    > >>
    > >> 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


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1