VBA code to make CUSTOM VIEW fill ANY RESOLUTION SCREEN

  • Thread starter Thread starter www.ttdown.com
  • Start date Start date
W

www.ttdown.com

I am trying to get VBA code to make an Excel CUSTOM VIEW that fills
the screen on a low resolution monitor, also (more or less), fill the
screen on a high or medium resolution monitor.
I a getting pretty desparate now!
Can anybody PLEASE help?

Darryl
(e-mail address removed)
 
Hi Darryl

This will fill any Excel window, regardless of window state and screen
resolution, with Columns A to H:

Sub ZoomUs()
Columns("A:H").Select
ActiveWindow.Zoom = True
ActiveCell.Select
End Sub
 
Dear Harald

Thanks so much for the reply and the neat code that does exactly what I
asked.

UNFORTUNATELY I did not word my request well.

I have a very large spreadsheet that uses code to navigate to any one of
about 200 CUSTOM VIEWS.

Each custom view is the same size as any other but each contains
different data for different people.

Everything worked fine until I loaded a demo onto my boss's computer.

He has a much higher res screen so my precisely defined block of data
ends up as a tiny square on his screen.

I need code that will make a block of cells defined by the "View",
"Custom View", menu command as say "Employee_47_Month_November"
reasonably fill any screen whether 640x480 or 1600x1280. Doesn't have to
fill exactly, as long as all cells in the custom view are visible.

I am begining to wonder if it is possible.?????

Thanks again Harald

Darryl
(e-mail address removed)
 
Darryl,

Something like this might work. Obviously, you'd need to loop thru
every view and replace "View1" with each view's name. I've never used
custom views, but this seemed to work for me assuming I understand the
problem.

Sub ZoomUs()
ActiveWorkbook.CustomViews("View1").Show
ActiveWorkbook.CustomViews("View1").Application.Selection.Select
ActiveWindow.Zoom = True
ActiveCell.Select
End Sub

Regards,
Steve Hieb
 
Hi

You could you the Application.UsableHeight propery to get the screen
size and use it to calculate a value for the Zoom property

Get the Application.UsableHeight value for your screen when the zoom
setting is at 100% on the screen you designed the worksheet or view
and use it in the following sub.

Sub do_zoom()

Dim A_height as long
Dim z_val as long

A_height = Application.UsableHeight
z_val = ((A_height / height at 100%) * 100) - 10
'subtract 10 or any value you choose to make sure it fits within the
screen
ActiveWindow.Zoom = z_val

End sub

Call this from the Worksheet_Activate event

This works for me

Simon Verity
 
Back
Top