option group

deb

Active Member
Joined
Feb 1, 2003
Messages
400
I have a form with 2 buttons. One displays a Pivot table and the other displays a pivot chart.

I would like to have an option group that gives the option of displaying the "totalsUSD" field or the "totalsEuro" field or both in the pivot table or the pivot chart.

The query has both fields in the query that supports both pivots

So, you can choose the currency type and then click the pivot Table or pivot chart button to display the data with the chosen currency.

can anyone help?

Thanks,
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Deb

This can be done with VBA and an option group on your form. Create an option group with 3 choices : first = USD, 2nd = Euro and 3rd = Both. Call the option group 'Currency' (under properties), make sure that USD = option 1, EUR = 2 and both = 3. Choose a default for the option Group (3 perhaps?)

For the purposes of this example, I have assumed the USD field in the report is called 'totalsUSD' and the Euro field is called 'totalsEuro' and the report is called MyReport. Be sure to use the actual field and report names. Change the code behind the print preview button by adding the following before your existing commands :

Code:
        DoCmd.OpenReport "MyReport", acViewDesign, , , acHidden
        Select Case Me.Currency
            Case 1
                Reports![MyReport].totalsUSD.Visible = True
                Reports![MyReport].totalsEuro.Visible = False
            Case 2
                Reports![MyReport].totalsUSD.Visible = False
                Reports![MyReport].totalsEuro.Visible = True
            Case 3
                Reports![MyReport].totalsUSD.Visible = True
                Reports![MyReport].totalsEuro.Visible = True
        End Select
        DoCmd.Close acReport, "MyReport", acSaveYes

This code should be inserted somewhere between the lines "Private Sub..." and "DoCmd.OpenReport..." in your existing code.

Repeat for your other report / print button.

HTH, Andrew :)
 
Upvote 0

Forum statistics

Threads
1,211,710
Messages
6,103,452
Members
447,866
Latest member
bowers261

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top