Embed Charts on MultiPage

[IgnBan]

Need help showing several charts (already on same workbook 8+), in a stationary MultiPage.
I have a report 18 Sheets that I need to summaries all data in one screenshot, kind like a Switchboard. My idea is to use the MultiPage with embed charts so by clicking in one button/tab user can see a chart and compare with relevant data in same sheet.
The summarized data with Multipage will be the 1st page when the workbook is open and will put some hyperlinked instructions if user needs to detailed data of charts.
How can I show just the Multipage without the Form in the background, how can I show it so when the workbook is open the Multipage be in a specific place like it is part of the Sheet without showing the From on the background? Is there any way to dynamically link the data to the embed charts on the Multipage, so when source data is updated the charts update too?

Thanks in advance so the replays !

[Bob Phillips]

Why not just use a dashboard worksheet? Much simpler.

[IgnBan]

El Kix, thanks fro replaying;

The reason is that 2/3 of the visual page is already full with summarized data from the other Sheets and if I shrink the 8+ charts to fit the rest of the space they won't be understandable/readable, I have a administrator rights so I can change the resolution on my computer by the 30+ people viewing the report are set to 600 x 800 resolution. The decision of use the multipage is to fit all the charts in the rest of the space, this way the charts will be to a size where you can see very clear the data point, axis values, etc, and at the same time overlap them to have them all in the same place. Hope this help visualize what I'm trying to accomplish.

[Bob Phillips]

You can use mutiple sheets to do the same thing , with links into the other pages. For instance, you could have a summary chart, and clicking on that chart will open up another work sheet with a set of related charts.

It is possible to embed pictures on a form, but it is not trivial. Worksheets are simpler to work with.

[IgnBan]

El Kix, hanks for replaying and giving feedback

You can use mutiple sheets to do the same thing

I?m already using several sheets (is what the report is),

with links into the other pages

as you can read in the original 1st post is what my intention is, if the recipient needs more detail on the small chart he will click on a hyperlink to the original full feature chart. I will be removing the "X" axis, Title, etc, on the small charts to make it more readable and fit properly in the small area.

you could have a summary chart, and clicking on that chart will open up another work sheet with a set of related charts.

On the report there is already a Sheet that contains 4 small charts (including several series), but my idea is to have all of them and all data numbers of the whole report summarized in one Sheet, Senior staff usually don't go through the whole report. I know I can make an interactive report, put buttons or hyperlinks to go back and for, but when you have all the data summarized in front of you, you can make more efficient decision , because you can relate and compare data. I want them to just open the report see the summary of all data , mangers can click on individuals sheets to see more detail of data to make changes to their processes.

Is there any body out there that can help me find a solution? I've been in several Forums searching for an example with no luck.
What I'm thinking now is may be mimic a MultiPage form, instead use several command buttons on top of a Rectangle (or any other object) the size of the 1/3 remaining space and "link it" to a dynamic chart. If you use the "Camera" tool (Tools->Customize->Tools->Camera) of Excel you can "take" a picture of a range and paste it in another page, this picture is dynamic, if the data changes in the original page the changes are visible in the pasted "picture .How can I use an object in a Sheet and mimic these feature with Charts?

Any feedback is greatly appreciated in advance.

[Bob Phillips]

If I am reading you correctly, you are accepting my suggestion, that is doing it al on worksheets.

Don't use the camera like that, it is a useful facility but not good in that respect. I would do it by having all my charts off-picture, and when I click whatever the trigger is, I would move that picture into the picture, first move all off. Something like this very simple example which has two buttons assigned to the same macro, and two charts

[vba]

Public Sub ManageCharts()
Dim mpChartObj As ChartObject

'move every shap off-picture
For Each mpChartObj In ActiveSheet.ChartObjects

mpChartObj.Left = ActiveSheet.Range("GA1").Left
mpChartObj.Top = ActiveSheet.Range("GA1").Top
Next mpChartObj

'now test which button pressed and move that chart on-picture
Select Case Application.Caller

Case "btnChart1": Call MoveChart("chartData1")

Case "btnChart2": Call MoveChart("chartData2")

'etc.
End Select

End Sub

Private Sub MoveChart(ByVal ThisChart As String)
With ActiveSheet

.ChartObjects(ThisChart).Left = .Range("G5").Left
.ChartObjects(ThisChart).Top = .Range("G5").Top
End With
End Sub
[/vba]

Don't forget, a chart is just a shape, so you even assign a macro to it.

[IgnBan]

El Kix, thanks again for providing expert feedback .

I don't follow the call back to range ("G5") how the code know the name of charts.
I got you code on a Workbook and it did move the charts off the view, but can not make them move back to range ("G5") . What is the way to name the chart?. I think this is my problem. This is what I did; I made the charts and name them dataChart1, dataChart2 and then I put them on sheet, then on Chart->Location move them on Sheet where the buttons are, run the macro it move the chart to GA1, but didn't move the dataChat1 back to ("G5"). Can you put the code on a sample workbook so I can follow the Call MoveChart.

Thanks again!

[Bob Phillips]

You have to name all of the buttons and the charts, and link them via the code.

Here is a small example

[IgnBan]

El Xid, thanks again for you replay.
I still don't know how you name the charts . If I follow the step that I described above to name the charts then I can not recreated it as you example, because as the y get to the Dashboard Sheet I can not see their properties, the Properties window is blank, am I missing a reference in the VBA ? Do you mind explain the step you took to name the objects (charts and buttons), or describe you code.
I think this is an amazing code, I 'm always looking for a way to save space and be funcional and with you "almost" UDF will be making very productive reports, specially Dashboards.....will name them to your honor! "El Kix Dashboards!

Thanks Again

[Bob Phillips]

To name a chart, just shift-click the chart. Rather than the little black square handles, you will get little whote round handles. The chart must be not selected before doing this.

Once selected like this, you can type the chart name into the Names box.

To name the buttons is simpler. I used Forms toolbar buttons, and I just selected them, then typed the name in the Names box.

PS it is El Xid, after the Spanish hero.

[IgnBan]

El Xid, first of all I apologized for misspelling you nick (I think is the second time) so double my apologies. Second now I get it! now I know how to get this code a run for a good Visual Business Intelligent Switchboard! I think this is going to be a good tool for my constant drive of improving my reports. Very cleaver code, screen space saver (even Microsoft thinks that way now with the new ribbon instead of the old pull down menu) and most important, a way to mimic that MiltiPage without any complicated forms!

Gracias El Cid.
Moderator you can close this post. Resolved by El Xid

[Bob Phillips]

No problem mate. My moniker is xld (ex-el-dee), but Simon Lloyd came up with the littel avatar, and it is a bit of fun, if inaccurate.

BTW, where do you get the idea that MS think of screen saving with the ribbon, it is a screen grabber, dynamic maybe, but still a grabber.

In case you are interested, here is a slightly different approach.

[IgnBan]

Xld, thanks again for the solution, and for the extra example. One question can this code be use in an Sheet where already have one chart that I don't want this to move with the others in the "PseudoMultiPage? let say I have "chart1" in range B2, how can I exclude this chart from moving with the others?

[Bob Phillips]

You would need to exclude it in the move code

[vba]

Public Sub ManageCharts()
Dim mpChartObj As ChartObject

'move every shap off-picture
For Each mpChartObj In ActiveSheet.ChartObjects

If mpChartObj.Name <> "chart1" Then

mpChartObj.Left = ActiveSheet.Range("GA1").Left
mpChartObj.Top = ActiveSheet.Range("GA1").Top
End If
Next mpChartObj

'now test which button pressed and move that chart on-picture
Select Case Application.Caller

Case "btnRegionalSummary": Call MoveChart("chartRegionalSummary")

Case "btnRegionalPie": Call MoveChart("chartRegionalPie")

Case "btnStateSales": Call MoveChart("chartStateSales")

Case "btnStateBudget": Call MoveChart("chartStateBudget")
End Select

End Sub
[/vba]

[IgnBan]

...And if I wanted to exclude two (I'm trying to use the operators And and Or, unsuccessfully ) will this modify the whole struct?

[Bob Phillips]

It is exclusive so it would be And.

[IgnBan]

Can somebody help, I need to exclude two charts from the move ("chart1", "chart2") Tried AND operator and ElseIf with out getting a error.
[VBA]

Public Sub ManageCharts()
Dim mpChartObj As ChartObject

'move every shap off-picture
For Each mpChartObj In ActiveSheet.ChartObjects

If mpChartObj.Name <> "chart1" Then

mpChartObj.Left = ActiveSheet.Range("GA1").Left
mpChartObj.Top = ActiveSheet.Range("GA1").Top
End If
Next mpChartObj

'now test which button pressed and move that chart on-picture
Select Case Application.Caller

Case "btnRegionalSummary": Call MoveChart("chartRegionalSummary")

Case "btnRegionalPie": Call MoveChart("chartRegionalPie")

Case "btnStateSales": Call MoveChart("chartStateSales")

Case "btnStateBudget": Call MoveChart("chartStateBudget")
End Select

End Sub

[/VBA]

[Bob Phillips]

As I said, use And

[vba]

If mpChartObj.Name <> "chart1" And mpChartObj.Name <> "chart2" Then
[/vba]

[IgnBan]

Spend an hour even tring you suggestion, but found that at the begining before tring the And added and End If, and didnt removed. My mistake. Thanks Xld.

[JonPeltier]

Need help showing several charts (already on same workbook 8+), in a stationary MultiPage.
I have a report 18 Sheets that I need to summaries all data in one screenshot, kind like a Switchboard. My idea is to use the MultiPage with embed charts so by clicking in one button/tab user can see a chart and compare with relevant data in same sheet.
The summarized data with Multipage will be the 1st page when the workbook is open and will put some hyperlinked instructions if user needs to detailed data of charts.
How can I show just the Multipage without the Form in the background, how can I show it so when the workbook is open the Multipage be in a specific place like it is part of the Sheet without showing the From on the background? Is there any way to dynamically link the data to the embed charts on the Multipage, so when source data is updated the charts update too?

Thanks in advance so the replays !

I realize this thread has gone on a long way; I usually ignore threads with more than about five replies. I would suggest that you're going about it the wrong way.

A dashboard should contain all the data you need and none that you don't need, in a single view (i.e., printed page or screen). The data should all be shown at one time, not by interactively moving one chart or another into position. It's far easier to compare charts which are visible together than charts which are only visible sequentially. You can shreink charts considerably, and if you take pains to ensure that they have identical scales and formatting, they will be readily understood.

In general charts are preferred over tables, but if the data is simple enough, a table may be sufficient. If you think you require tables and charts of the same data, consider whether one or the other is redundant.

I refer you to Charley Kyd's Excel User site, which contains a lot about creating and maintaining effective dashboard reports:

http://www.exceluser.com/dash/index.htm

Admittedly, the site is attempting to sell you Charley's dashboard products (which would probably be a worthwhile investment), but he also offers many free articles with excerpts from his book and workbooks.