Sheet Size 1

[Stretchwickster]

Is there a method I could use in order to get the height and width of the VISIBLE part of the sheet. I've tried using Application.Height and Application.Width but these obviously return the size of the application not the cell area.

Anyone got any ideas?

Basically what I want to do is size a ChartObject so that it fills the visible sheet area regardless of the screen resolution.

Your help would be much appreciated!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Hi
This should (I stress should) do the trick though I've had problems that I can't explain with this type of thing in the past.

Sub a()
With Worksheets("Sheet1").ChartObjects(1)
    .Top = 1
    .Left = 1
    .Width = Application.UsableWidth
    .Height = Application.UsableHeight
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Little addition which explains a lot. No idea why I'd never looked at this before - just stoopid I suppose.

Extract from Help for UsedWidth (well not an extract it's all there is!)

"Returns the maximum width of the space that a window can occupy in the application window area, in points. Read-only Double."

In essence this means that you can't see what's behind the 2 scrollbars and sheet tabs.

I have no idea how to check the width of these items (maybe an API) to subtract from the width/height of the object so I'd initially go with removing them from the view. Bit of a pain with the sheet tabs though?!!?

I'll get me coat!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Stretchwickster]

Loomah,

Cheers for the UsableHeight and UsableWidth commands (I didn't know about them!) - it looks much better than it did - although, as you say, the edges of the chart are obscured. I do, unfortunately need the sheet tabs so I can't hide them. A star for your helpfulness.

Does anyone else have any ideas?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[ssVBAdev]

Well, this is not perfect but maybe it'll give you some more ideas... (Keep in mind I only tried this on my machine and not others. There very well may be some issues that I have not addressed [ie. not thought of ;-)])

Lets just say we knew a range of cells which were viewable on the screen. I just happened to pick A1 down to O36 (this is the range of cells that are FULLY within my screen when I open Excel with my system configuration).

Lets find the Top and Left of the Range (Top and Left of cell A1) and the Height and Width of the Range (the accumulative totals of the Heights and Widths of the Rows and Columns, respectively, within the range).

Then, Check these totals with the actual Applications Usable Heights and Widths and size the Object appropriately. But, lets also take off a 'certain' percentage of the Usable Area for Screen Real Estate that is occupied by ScrollBars, Sheet Tabs Etc... ('certain' meaning that I have no real idea how much space these things take up but at least we are making an allowance for them)

Then apply these Properties (Left, Top, Height and Width) to the Chart Object and... Voila! A Chart that is FULLY within the viewable area of the screen.

Option Explicit

Sub Test()
Dim R As Integer
Dim C As Integer
Dim H As Integer
Dim W As Integer

H = 0
W = 0

With Worksheets("Sheet1&quot
For R = 1 To 36
H = .Cells(R, 1).Height + H
Next
For C = 1 To 15
W = .Cells(1, C).Width + W
Next
End With

With Application
If H >= .UsableHeight - (.UsableHeight * 0.05) Then
H = .UsableHeight - (.UsableHeight * 0.05)
End If
If W >= .UsableWidth - (.UsableWidth * 0.05) Then
W = .UsableWidth - (.UsableWidth * 0.05)
End If
End With

With Worksheets("Sheet1&quot
With .ChartObjects(1)
.Top = Cells(1, 1).Top
.Left = Cells(1, 1).Left
.Height = H
.Width = W
End With
End With
End Sub

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Stretchwickster]

Nice idea ssVBAdev, however there is a problem with this:

>Lets just say we knew a range of cells which were viewable on the screen. I just happened to pick A1 down to O36 (this is the range of cells that are FULLY within my screen when I open Excel with my system configuration).

The problem is that with different screen resolutions the viewable cell area changes i.e. when my screen res is set to 1152x864 I can see the complete range A1-P39 and when the screen res is set to 1024x768 the complete visible range is A1-N33. Therein lies the problem.


Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Hi again Stretch

Thsi is an adaption of something I have dug up elsewhere. This still isn't perfect but it brings the chart inside the viewable area rather than outside as with my first posting.

Sub a()
Dim rng As Range
Worksheets("Sheet2").Parent.Windows(1).Zoom = 100
Set rng = Worksheets("Sheet2").Parent.Windows(1).VisibleRange
Application.Goto rng
rng.Parent.Parent.Windows(1).Zoom = True
Set rng = Worksheets("Sheet2").Parent.Windows(1).VisibleRange
    With Worksheets("Sheet2").ChartObjects(1)
        .Top = 1
        .Left = 1
        .Width = rng.Width - (rng.Width / rng.Columns.Count)
        .Height = rng.Height - (rng.Height / rng.Rows.Count)
    End With
Range("a1").Select
End Sub

The calc at the end for the width is a mashed up thing to allow for different row heights & column widths. Without it the code makes the chart too big (again!)

Hope this is better.
Just one thought though, if you want a full sized chart why don't you create it as a chart sheet. Will still need some manipulation probably but.......

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[ssVBAdev]

Then (if I may borrow a snippet from Loomah's post) zoom into the range that you want first
ie...
Range("A1:O36&quot.Select
ActiveWindow.Zoom = True

If it is critical that you maintain the zoom when any data behind the chart is viewed, then record the current user's zoom into some obscure cell somewhere and us it to restore the users zoom later.

But, I do agree with Loomah in that you might want to look into a chart sheet. and again, the 'manipulation' that may be involved might only be a simple zoom.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Stretchwickster]

Ok thanks guys - I'll try that out.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)