Make allowances for user's monitor settings

Topic

New Reply

I fear that I know the answer to this before asking, but I have done that previously, and been pleasantly surprised……

Is it possible to control the zoom setting of a worksheet, in line with a users monitor size / settings. For example, A1:V39 is the range that is required to be ‘seen’, designed on my pc to fit perfectly to my screen size. However, other users may have smaller or larger screen’s, can this then be manipulated to a perfect fit?

Any tricks?

Reply | Quote

Replies

Do you mean something as simple as selecting A1:V39 and then Zoom to Selection?

[*]Excel 2007 this is on the View tab, Zoom to Selection button
[*]Excel 2003 this can be done with the arrow next to the toolbar control for changing zoom level
[*]VBA
[*]Activeworkbook.ActiveSheet.Range(“A1:V39”).Select
[*]ActiveWindow.Zoom=True

[/list]

Reply | Quote

Yes, I think…. Thanks

I have several worksheets within a workbook where I want to avoid the user needing to scroll horizontally. I would like to use the ws activate event, assess the used columns range, and fit accordingly. Something along the lines of:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.ActiveSheet.Range("A1:W1").Select 'How to identify used column range?
ActiveWindow.Zoom = True
ActiveWorkbook.ActiveSheet.Range("A1").Select

End Sub

Reply | Quote

I don’t think you’d want to use the SelectionChange event – this would fire each time the user moves to another cell.

You could change the line

ActiveWorkbook.ActiveSheet.Range(“A1:W1”).Select

to
ActiveSheet.UsedRange.Rows(1).Select

Reply | Quote

Yes, it’s the sheet activate procedure I am using, sorry for the confusion.

Is the ‘select’ necessary here?

Code:

Private Sub Worksheet_Activate()
	Application.WindowState = xlMaximized
	ActiveWindow.WindowState = xlMaximized
	ActiveSheet.UsedRange.Rows(1).Select
	ActiveWindow.Zoom = True
	ActiveWorkbook.ActiveSheet.Range("A1").Select
End Sub

Reply | Quote

The line

ActiveSheet.UsedRange.Rows(1).Select

is necessary here because the line

ActiveWindow.Zoom = True

adjusts the zoom percentage so that the current selection fits in the window.

Reply | Quote

Thanks for the clarification.

Reply | Quote