Excel 2010 - How do you fix the width of a frozen merged cell to automatically fit the screen?

Question

I'm producing a spreadsheet for work that has a header of merged cells at the top. I have frozen the row so it is visible when you scroll down. All the computers in my office have different screen resolutions and versions of MSOffice so when I've made spreadsheets in the past, the appearance has been a lot different to how I wanted it to be. Is there any way of setting the selected cells to auto fit to the screen?

TIA!

Adam

Answer 1

EDIT-



i guess you can hide all the extra columns

assume your sheet table data is in columns A to Y

- click on Z (so you select the whole column)

- hit Ctrl Shift Right Arrow so that you select all the columns to the right

- then right click on column label XFD

- click Hide

then noone has a choice to go beyond your set limit



or else go to every computer manually and harass the users until they conform to the resolution you want. :o(





original-

you are going to need to put VBA code in your sheet.

heres the website that has the code



http://www.vbaexpress.com/kb/getarticle.php?kb_id=337



you can just copy&paste the code

the site has instructions on how to use it. i have never tried the code.



i will just copy&paste the code for you here in case the web site goes down for some reason.





'<< CODE FOR THE "ThisWorkbook" MODULE >>



'<< NOTE THAT SCREEN HEIGHT & WIDTH ARE >>

'<< MEASURED IN PIXELS, WHILE APPLICATION >>

'<< & WINDOW HEIGHTS AND WIDTHS ARE >>

'<< MEASURED IN POINTS. (pixel ~ 3/4 point) >>

Option Explicit



Private Sub Workbook_Open()



'N.B. a screen resolution of 800x600 pixels was used for this E.G.



'//Obtain current users screen width & height (in pixels)

Run ("MonitorInfo")



With Application

'cancel any xlMaximized

.WindowState = xlNormal



'<>

'//POSITION WITH RESPECT TO MONITOR

.Top = 1 '< points

.Left = 1 '< points



'//WIDTH

'replace 400 with the width you want

'replace 800 with your screen width

.Width = 400 * ScrWidth / 800



'//HEIGHT

'replace 300 with the height you want

'replace 600 with your screen height

.Height = 300 * ScrHeight / 600



With .ActiveWindow

'cancel any xlMaximized

.WindowState = xlNormal



'<>

'//POSITION WITH RESPECT TO APP. WINDOW

.Top = 1 '< points

.Left = 1 '< points



'ZOOM

'replace 800 with your screen width

.Zoom = 100 * ScrWidth / 800 '< 100 is %



'WIDTH

'replace Application.UsableWidth

'with a number for the width you want

.Width = Application.UsableWidth



'HEIGHT

'replace Application.UsableHeight

'with a number for the height you want

.Height = Application.UsableHeight



End With

End With

End Sub











'<< CODE FOR THE STANDARD MODULE >>



Option Explicit

Public ScrWidth&, ScrHeight&

Declare Function GetSystemMetrics32 Lib "User32" _

Alias "GetSystemMetrics" (ByVal nIndex&) As Long



Private Sub MonitorInfo()

ScrWidth = GetSystemMetrics32(0) '< in pixels

ScrHeight = GetSystemMetrics32(1)

End Sub

Answer 2

Without macros being enabled, you cannot make the workbook automatically adjust the on screen display Zoom setting to produce a 'Full Screen' effect.



Your options then, to me, are to survey the users and determine the range of screen resolutions in play. The dimension that matters in a screen resolution is the first one, 'width', since you probably will be scrolling vertically anyway.



Once you have the range of resolutions, you would have to calculate the 'Zoom' factor for each compared to the resolution you developed the app in.



For example, if you developed in 1280 x720 the 1280 would represent a Zoom setting of 100 (%). So, if you had a user with a resolution of 1024 x768 then the Zoom factor for that would be 80 (%).



You would have to access the properties for each worksheet in the workbook and set the Zoom property to 80. Save the workbook as 'My Workbook - 1024' or something. Then, assign the application to each user based on the resolution they are using.



Alternately, you could include instructions for the user to set the Zoom setting for each sheet themselves, based on their screen resolution.



Without macros, it is a beach. With macros, it is quite simple. I always include my own automatic routine in my applications, along with a 'manual adjustment' macro the user can use to tweak the Zoom in increments of plus or minus 1%, and then lock that custom setting for all sheets. My code is much simpler than the code suggested, but works perfectly.



If you pursue the 'without macros' course, and need conversion tables for the resolutions let me know the resolution you developed it in and I will provide the conversion Zoom factors.

Answer 3

Most individuals have given you excellent solutions, handiest a ways round. Simplest method to widen a column to only the right amount bearing in mind what is contained, get your mouse pointer onto the correct border of the column handle (i.E. The A, B etc), the mouse pointer turns to a massive black 4 pronged arrow, double click on and the whole column adjusts automatically. Nonetheless, if you want a distinctive width, correct button on the column handle right down to width and put in what you want. The average default is I believe eight.Seventy five.