Is there a way to take a graph from excel and display only the graph on the screen, hiding the rest of the spreadsheet.

I would suggest you to use different sheets in the spreadsheet for Graphs and Data.
The Data containing sheet can be hidden by default, and you can unhide it if required.

Its not just the numbers I want to hide.  I want to hide all of the frame, headers etc as well.  And would like to toggle back from and forth.

I guess in order to do that you will have to make a small program in visual studio to open the excel file, take out the graphs, display them and keep them updated.
You can hide a lot in excel but some items will always be visible.

not telling me how to hide the frame, headers and how to toggle back and forth.

suggest you show an example

If your intent is to hide everything except graphs, I suggest you to take VBA Route.
For example using the following code in the excel would open it in full screen.

Private Sub Workbook_Open()
     Application.DisplayFullScreen = True
End Sub

Select allOpen in new window

Hope this helps.

helpful  - But note that the graph does not occupy an entire worksheet.  I only want to see the graph - not the surrounding rows and columns. that is, I don't know how to use this to isolate the graph subset of a larger spreadsheet.  
And, to repeat once I have an isolated view of the graphs, I need a way to go back and forth.

Everyday you learn something new ;-)

i suggest you take the code of pritamdutt and perhaps add a few lines to it to position the graphs on the screen

Public Sub set_presentation_mode()
    Application.DisplayFullScreen = True
    Application.WindowState = xlNormal
    Application.DisplayFormulaBar = False
    Application.DisplayScrollBars = False
    Application.DisplayStatusBar = False
    Application.Width = 200
    Application.Height = 200
    Application.Top = 100
    Application.Left = 800 - Application.Width
End Sub

Public Sub reset_presentation_mode()
    Application.DisplayFullScreen = False
    Application.WindowState = xlMaximized
End Sub

Select allOpen in new window

and assing key combinations such as F12 to set and shift F12 to reset or something.
please experiment with the width/height/top/left figures to match you situation.

to select a graph you can use

public sub set_presentation_mode()
    worksheets("sheet with chart").select
    worksheets("sheet with chart").range("B36").select
    [....]

Select allOpen in new window

Furthermore, it might be wise to use a reset function like

Public Sub reset_presentation_mode()
    Application.DisplayFullScreen = False
    Application.WindowState = xlMaximized
    Application.DisplayFormulaBar = True
    Application.DisplayScrollBars = True
    Application.DisplayStatusBar = True
End Sub

Select allOpen in new window

If you have made your graph, right click it, and choose 'move chart'.
Then choose 'New Sheet'.
Your chart will then be separate from the worksheet on a screenfilling page.
You can toggle up and down the sheettabs with Ctrl + PageUp , Ctrl + Page Down.
You could however, draw some rectangles on your worksheet and separate charts and assign a macro to it like:
'rectangle on worksheet
Sub Rectangle1_Click()
Sheets("Chart1").Select
End Sub

'rectangle on chart tab
Sub Rectangle2_Click()
Sheets("Sheet1").Select
End Sub

Thats seems closer to what I am looking for.  It will take a day or two to try this out.  thanks

Sorry this took so long -- this is to akoster, or anyone else

the shape of my graph is wide and narrow.  When I move the graph to its own tab the graph only fills the whole screen and its dimensions are not what I want.   What is the best way to isolate the graph (or graphs) so all I see are those graphs in exactly the same size and aspect ratio that I first built it, and so I see that graph (or graphs) and nothing else (no margins, headers..) in the window but my graph?

What about sizing the Excel application window manually to the graphs dimensions?
 Write a macro which strips the tabsheets, scrollbars, ribbon,... and assign it to the graph. Write the macro that when the graph gets clicked once more the ribbon & all will be back.

Your example has two frames -- and the gaph has a ribbon at the top.   As to manually sizing the excel frame to the graphs I am able to move one corner to correspond the one corner of the graphs but can't seem to get the other 3 corners to match.   However perhaps I can hide unwanted rows and columns.   I will try that.    I thought perhaps I could put the exact location of the graphs into the VB command that manipulates the window.

dma70,

when you move the graphs to have each graph on a separate page, they will fill out to page size.
When you do not want this, you could either

 - scale down the 'plot area' of the graph sheet in which the graph itself is drawn

or

 - place the graph inside of a regular worksheet to determine a fixed format. as an example :

 example-graphs.xlsx

sheet1 contains an 8x12cm graph which is is placed on its own sheet (graph1) and resized to 8x12cm on its own graph2 sheet.

using the first option, on selecting the graph you should ensure that the size of the excel window is adapted to exactly fit the graph.
using the second option, the actual position of the graph on the otherwise empty sheet should be such that the sized window displays the graph and not the empty parts of the sheet.

I am sorry to be so persistent, but it would really help if you incorporated your VB code into the graph example you sent.  This way I can run the macros and see for myself how to shrink the screen so it only shows the graphs, and how to go back to original situation.   It would be most useful if I could see how it is done in all three cases you show (one for each tab).

thanks

no problem, you can use this :

example-graphs-v2.xlsm

double click on the plot area of any chart to activate presentation mode for the selected chart

some progress.

However the macro set_presentation_mode only works with Grafiek1 - which fills the screen.  I still cant dimension the graph to any size I want and get it to shrink down with the frame fitting the graph.

Also how do you suggest I execute reset_presentation_mode, once I have shrunk the screen and have no ability to get to the macro menu.   Is there a way to creat a button that will stay with the graph to reset it back?

The key of setting the dimensions of the graph is in the lines

    Application.width = Worksheets("Blad1").ChartObjects("Grafiek 1").width
    Application.Height = Worksheets("Blad1").ChartObjects("Grafiek 1").Height

which currently take the size of the graph on sheet blad1. You can easily change this.
Currently the macro's are started by double clicking on the graph itself, but incorporating a button is certainly possible.

As long as you align the left top corner of the graph with a cell and make sure that the button stays inside of the graph (otherwise you will not be able to get back), you can use this example-graphs-v3.xlsm.

The reason for aligning the graph within a cell is because the final visibility window is sized according graph dimensions and placed to match the top-left cell of the graph. So iy you place the corner in the middle of a cell, you will see part of the cell next to the graph itself and miss part of the graph.

I am really sorry.  But I just can't figure out what to do.  Too many of the instructions are piecemeal.
For instance the code you refer to in your message does not exist anywhere in the macros.

All I see are lines like application.width = chart.width  in the sub set_presentaiton_mode

 I  need a workbook with your 3 tabs.  For each tab I need a macro that shrinks the graph, and each needs a button that restores it to its original dimensions.    Without that, I am simply at a loss at which of your various pieces of code to include and which I should not.  

I'm sorry.

It's just the way that I normally work : i more or less assume that a question is asked because you want to know more about how things can be done or what is causing particular behavior, such that you can use this knowledge to your advantage. You could on the other hand also be looking for an answer and do not really bother how it is made.

i'll prepare an example for you, it'll need some time because it will be a busy day today...

that is fine -- at this point it is far more important that it works correctly AND leaves my excel intact when it is finished.   I have been having trouble with running the "reset" function and finding it doesn't complete accurately.. that is a scrolbar is missing for instance.

Hmm,

i noticed some strange behavior as well but was working in multiple macro-enabled worksheets at the same time for quite a while. Normally restarting excel solves this problem but in your case it might be best to adapt to a slightly unconventional approach :

we could also instantiate a new excel application, copy the graph, format it to show only the graph. That way you could close the new excel application when you do not need it any more, and the original form stays intact.

Would this be a plausible solution ?

That won't do.   The graph is displaying real time data and I want to make the application for someone else who would only care to see the graph and nothing else.

Not a very good idea then.

At my workplace (Office 2007), both horizontal and vertical scrollbar re-appear after switching back & forth multiple times.
Do you miss a single scrollbar both bars ?

DMA70,

would this be a workable solution ?
It would be a shame to give up when the solution should be in close reach
example-graphs-v4.xlsm

akoster:

Ok - this spreadsheet seems to work.   Just a few more things and I think I will be able to use it.     Can you explain to me how the program knows what the dimensions of the graph?    I might want to have two graphs next to each other in the "window."  How would I do that?   In general I would like to know how to specify what part of the graph or, for that matter a part of the spreadsheet, should appear in the window.


While I am focused on making frames for graphs - the generic need is to isolate apart of the spreadsheet so only that part is seen in the window.  That could include multiple graphs, or cells or a combination of graphs and cells.  Just think about it as "framing."

thank you

Glad to see that we are getting to the solution you are looking for !

The dimensions of the graph on 'blad1' are taken from the size of the chartobject (in the example this chartobject is called is "Chart")

set_alternate_presentation_mode Chart.topleftcell, Chart.width, Chart.height

Select allOpen in new window

this defines the width & height of the visible window (the 'frame') and the top left cell that is visible.
When you have placed two charts next to each other, you could call the 'set_alternate_presentation_mode function specifying the topleft cell of the topmost and leftmost chart, and add the widths / heights of the other charts to display them both.

In the  file v4 the frame is sized and located to match the graph, but you could just as well set it to any arbitrarily chosen frame of cells.
These cells inside of the frame can contain one or more graphs but could also contain text, values or empty cells.

akoster:  Thanks for all your hard work.  

 I have now read this over several times.  I figure if I read it a few more times I might get it.  But I believe you infer that I know more than I do.   Here are my lingering questions:

I gather from your example I need to call the function set_alternate_presentation _mode with three parameters.  
1) the location of the upper left hand corner
2) the width of the "frame"
3) the height of the "frame

But I don't even know the dimensions of the location, width and height.  Are they specified in cell locations (eg B3:D6), or  in cm?  
Nowhere do I see an example where this is called with clear parameters (like numbers) - so I have no example to follow.
For example if my graphs are located between cells D5 and H10 how do I tell the function what to frame?

Perhaps the answer is I don't know understand what a ChartObject is and how to get the dimensions out of it, and how the program knows which chart I am referring to.

Also, What if I don't want the frame to occupy the upper-left-corner - how would I tell it otherwise.  Its ok if it is necessary - but I will have to make sure nothing else is of importance in the upper left corner.

Again -- its is best to give me a workable example where I see clearly how to frame my object.

Dennis

Dennis,

no problem, i'll guide you through.
If you look at the code in v5, you will see what dimensions you need for

1 : cell range
2 & 3 : double

if you click at the button on the blue frame, you will notice that the topleftcell D13 is used to locate the upper left corner of the 'visibility frame'. The width of the frame is set at 222 points (this is the width of the blue frame itself) but because the frame does not exactly match the cell D13, part of the frame is not visible.

if you click on the frameless button, it will use the values inserted at D27:D29. When the button is not visible any more after changing these values, locate it by using the up/down/left/right keys...

When you are more confortable working with cm instead of points, you could use

 h = Application.CentimetersToPoints(7)

or even

h = Application.CentimetersToPoints(range("D29"))

200 points roughly corresponds to 7cm.



example-graphs-v5.xlsm

akoster:   The network administrator closed the discussion last week and when I went to work on your lastest version it wasn't there anymore - so I asked that the question be reopened.  Thank you for your patience.    No I tried to bring up your version 5 and got errors.   I am enclosing an image of what my version of excel was objecting to.
This looks serious enough to disallow your new additions from working.  Please let me knwo what the next step should be as we are almost there and I do want this to work.      akoster-pics.doc

Dennis,

This is most likely caused by excel crashing on me while working on the file. I guess that somehow some references (eg the links between the graphs and the data on blad1) therefor were changed from 'blad1'!$B3' to '[d:\temp\example-graphs-v6.xlsm]blad1'!$B3' for example. Because your workplace differs from mine it would be an educated guess that those folders are not present at your location and thus the errors are given.

When i open the document i get no errors, so i think that if i save it as v6 (no internal changes) this will solve your problems.
Please let me know if this helps you out...


example-graphs-v6.xlsm

akoster:

Making progress -- your frameless button works just the way I want it.   thank you..

Hopefully we are near the end.  I have an existing spreadsheet that I want to enable with your VB.  I would like to use the Frameless procedure and put a button on the graph like you do with the frame procedure.

What code do I need to copy into my existing spreadsheet with the graphs and how ?

I tried copying most of the code to a single module in the existing graph, but I got an error saying it needed to go in an "object module."   For this reason it also stopped the other macros/buttons from working (the ones which are orginally associated with my graph spreadsheet).  

Could you advise on how to implement your good work.

Dennis

Implementing the code would be a two-stage rocket :

1) in the VBA code section of the worksheet where you have the button, include the code of the 'button_frameless_click()' routine.
make sure that the t, w & h variables link to the cell ranges you would like to use to supply the size of the resulting window & the left-top cell of the graph.
The window size can also be hardcoded, eg. by w = 300.

2) copy the 'reset_presentation_mode' and 'set_alternate_presentation_mode'  subroutines from the module and paste them in the same code section. They will work from there as well.

When you get an error saying that a specific variable needs to go in an object module, you probably did not copy the "sub [...]()" line with it. This indeed can prevent the other macros to work.

Please post the workbook you are working on if you have problems implementing the code, I can then help you out more efficiently

akoster:  I am very excited.   We are there.   I works beautifully.

I have what I hope is one last question which might relate to your code.    I have multiple screens.   When I move the spreadsheet away from the screen that named Screen 1 (the default screen for windows) it works find on the shrink function.  But when I click the button again to bring it back to a normal excel spreadsheet it jumps back to Screen 1. Is there some default that must be reset?

Dennis

Another addition.  The upon reset the macro puts the "topleft" cell in the upper left hand corner of the spreadsheet.   Can reset put you back to having A1 in the ulhc?

This took way to long to get an answer, but in the end I got all the answers and more.   The responder deserves an "A" for persistence.