[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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 have a two graphs that I have on a relatively complex spreadsheet, that updates in real time (adds a point every minute).   I would like to display this on my screen in the corner while I do other stuff.   However I don't want all the excel overhead with it.   However having a button or some other way of toggling back to the full spreadsheet would be required so that if I need to go back and change something I can.
0
dma70
Asked:
dma70
  • 19
  • 16
  • 2
  • +1
1 Solution
 
pritamduttCommented:
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.
0
 
dma70Author Commented:
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.
0
 
Arno KosterCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dma70Author Commented:
not telling me how to hide the frame, headers and how to toggle back and forth.
0
 
dma70Author Commented:
suggest you show an example
0
 
pritamduttCommented:
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

Open in new window


Hope this helps.
0
 
dma70Author Commented:
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.
0
 
Arno KosterCommented:
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

Open 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.
0
 
Arno KosterCommented:
to select a graph you can use

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

Open 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

Open in new window

0
 
Davy2270Commented:
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
0
 
dma70Author Commented:
Thats seems closer to what I am looking for.  It will take a day or two to try this out.  thanks
0
 
dma70Author Commented:
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?
0
 
Davy2270Commented:
What about sizing the Excel application window manually to the graphs dimensions?
 sizing excel application windowWrite 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.

0
 
dma70Author Commented:

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.
0
 
Arno KosterCommented:
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.
0
 
Arno KosterCommented:
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.
0
 
dma70Author Commented:
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
0
 
Arno KosterCommented:
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
0
 
dma70Author Commented:
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?
0
 
Arno KosterCommented:
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.
0
 
dma70Author Commented:
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.  

0
 
Arno KosterCommented:
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...
0
 
dma70Author Commented:
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.
0
 
Arno KosterCommented:
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 ?
0
 
dma70Author Commented:
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.
0
 
Arno KosterCommented:
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 ?
0
 
Arno KosterCommented:
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
0
 
dma70Author Commented:
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
0
 
Arno KosterCommented:
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

Open 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.

0
 
dma70Author Commented:
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
0
 
Arno KosterCommented:
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
0
 
dma70Author Commented:
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
0
 
Arno KosterCommented:
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
0
 
dma70Author Commented:
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
0
 
Arno KosterCommented:
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

0
 
dma70Author Commented:
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
0
 
dma70Author Commented:
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?
0
 
Arno KosterCommented:
Using multiple monitors to position the spreadsheet should be possible using

application.left = 0 for left monitor
application.left = [horizontal resolution of left monitor] for monitor on the right

I only have one monitor though, so i cannot test this for you.

for the second addition,

you could update the reset macro with a line like

activesheet.range("A1").select

Open in new window

0
 
dma70Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 16
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now