View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
TBD TBD is offline
external usenet poster
 
Posts: 41
Default How to size window to fit cells (not Zoom)

Follow-up:
Not to diminish the value of Barb's solution -
here's another approach which defines a range then gets the pixel width of
the range object.
(credit to "EVERSTRIVIN", at www.excelforum.com)

Private Sub WorkSheet_Activate()
With Application
'.DisplayStatusBar = False
'.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
.ActiveWindow.WindowState = xlMaximized
.Goto ("R3C2"), True

'Range("B3").Select
.Width = Range("B3:R3").Width
lowest = 1
For i = 2 To 14 Step 3
If Cells(200, i).End(xlUp).Row lowest Then
lowest = Cells(200, i).End(xlUp).Row
ElseIf Cells(200, i + 1).End(xlUp).Row Then
lowest = Cells(200, i + 1).End(xlUp).Row
End If
Next
.Height = Range("B3:B" & lowest + 1).Height + 74
End With
End Sub
--
The Cobra: A few Cobras in your home will soon clear it of Rats and Mice. Of
course, you will still have the Cobras.
(http://en.wikiquote.org/wiki/Will_Cuppy)


"tbd" wrote:

Greetings,
I'd like to size the application window so that it only displays a
rectangular region of cells that I can define programmatically.

If there's not an easy way to specify the right-most and bottom-most visible
cell, then, is there a way to read a cell's pixel position?

The following commands may be relevant:
Application.Goto gWS_Calendar.Range("A1"), Scroll:=True
and
Application.Width = SomeNumberOfPixels
Application.Height = SomeNumberOfPixels

Thanks/Cheers!

--
The Cobra: A few Cobras in your home will soon clear it of Rats and Mice. Of
course, you will still have the Cobras.
(http://en.wikiquote.org/wiki/Will_Cuppy)