MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Mystery - Experts stumped by Shrinking Column Widths


Posted by Susan on April 13, 2001 2:38 PM

Recap of Problem:

I write spreadsheets for 150 remote locations, 149 of which work with no problems. But at one location, the column widths I set appear to be automatically reduced to about 84% of the size I explicitly specified while developing, resulting in those nasty #### replacing large numbers. The files are fine when opened for the first time; the column width changes appear to happen during/after a save & reopen.

I've checked screen display settings (pixels), Excel Zoom, default font settings and default column widths, which are fine. The PC is a newish Dell running Windows 98. The models are locked and password protected, and the users are relatively low-skilled, so I don't think it's intentional hacking. Also, it's not just models I write that have the problem; even very simple models written by others and containing no VBA have the same issue.

Dave suggested imbedding an 'column.autofit' command in an auto-open macro, which would fix the problem. I appreciate the suggestion, but unfortunately, it would also destroy my formatting, so it's not really a viable fix.

Any additional ideas would be greatly appreciated!
Thanks - Susan


Posted by RS on April 13, 2001 4:17 PM

Presumably you mean that some columns widths should be different from the autofit width? I don't think any other formatting would be affected by autofit.
In the absence of a solution that gets to the root of the problem, instead of using autofit in in your auto-open macro, you could specify each column width.

Posted by Dave Hawley on April 14, 2001 12:34 AM

Hi again Susan

All the above seems to suggest a problem with the PC's Excel install. If they have they Office 2000 installed they could try the Detect and Repair option.

Below is a list of Start-up switches for Excel. They may help ?


XL2000: Startup Switches for Microsoft Excel 2000

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Excel 2000

--------------------------------------------------------------------------------


SUMMARY
Microsoft Excel accepts a number of optional switches that you can use to control how Excel starts. This article lists the switches and provides a description of each switch.

MORE INFORMATION

Switches
The following table lists the switches you can use to control how Excel starts. Switches are not case sensitive: /O is the same as /o. In some cases, you can use more than one switch at a time. If you do this, you must separate the switches with spaces, for example: /o /s
Switch Function
----------------------------------------------------------------------

/e, /embedded Forces Excel to start without displaying the
startup screen and creating a new workbook
(Book1.xls).

Example: /e, /embedded
/m Forces Excel to create a new workbook that
contains a single macro sheet.

Example: /m

/o Forces Excel to re-register itself. Specifically,
the following key is rewritten in the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel

NOTE: If this key contains invalid values, they are
not corrected by using this switch. This switch only
replaces missing values.

Example: /o

/p <folder> Forces Excel to use the path you specify as the active
path rather than the default path.

Example: /p "C:\Windows"

/r <filename> Forces Excel to open the specified file in
read-only mode.

Example: /r "C:\My Documents\Test.xls"

/s Forces Excel to bypass all files in the <Windows
Dir>\Application Data\Microsoft\Xlstart folder or the
alternate startup file location. It also forces Excel
to bypass the toolbar file (Excel.xlb or
<username>.xlb). You will see "Safe Mode" in the
Excel title bar. Use this switch when you want to
start Excel in safe mode.

Example: /s
/regserver Forces Excel to register itself and then quit. Use this
switch when you want Excel to rewrite all of its
registry keys and reassociate itself with Excel files,
such as workbooks, charts, and so on.

Example: /regserver


/unregserver Forces Excel to unregister itself and then quit.

Example: /unregserver
Using Switches When You Start Excel
Adding a Switch to the Microsoft Excel Shortcut
To add a switch to the Microsoft Excel shortcut:
Right-click Start on the taskbar, and click Open on the shortcut menu.


Double-click the Programs folder. Right-click the shortcut for Microsoft Excel, and click Properties on the shortcut menu.


Click the Shortcut tab.


In the Target box, type the switches you want after the command that runs Excel.exe. For example, if you want Excel to start without creating a new workbook, and the Excel.exe file is installed in the default location, you would type the following in the Target box:


"C:\Program Files\Microsoft Office\Office\Excel.exe" /e
NOTE: If the options on the Shortcut tab in the Properties dialog box are unavailable, your shortcuts are Windows installer shortcuts. To modify these shortcuts, please click the article number below to view the article in the Microsoft Knowledge Base:
Q229396 OFF2000: Unable to Modify Office Shortcuts
Click OK.


Running Excel One Time with a Switch by Using the Command Line
If you want to run Excel with a switch occasionally, you can add the switch to the command line. To do this:
On the Start menu, click Run.


In the Open box, type the path to Excel.exe and add the switch you want to use at the end of the command line. For example, if you want to bypass all files in the startup folders and the Excel.exe file is installed in the default location, you would type the following command:


"C:\Program Files\Microsoft Office\Office\Excel.exe" /s
Click OK.

Additional query words: starting start up startup XL2000 launching running activating command-line

Keywords :
Issue type : kbhowto
Technology :

Regarding the Autofit destroying formatting does this mean as RS suggests, you have some columns set wider than the Autofit ? As Autofit certainly wouldn't change any other formatting, except column width.

If the width is only shrinking on certain Columns and on certain Worksheets, house the AutoFit code in the Worksheet_Activate of those Worsheets only and set it for only those Columns.

BTW: I didn't suggest using Auto_Open, I suggest using the Workbook_SheetActivate.


Dave

Dave


OzGrid Business Applications

Posted by Susan on April 17, 2001 7:29 AM

Regarding the Autofit destroying formatting does this mean as RS suggests, you have some columns set wider than the Autofit ? As Autofit certainly wouldn't change any other formatting, except column width. If the width is only shrinking on certain Columns and on certain Worksheets, house the AutoFit code in the Worksheet_Activate of those Worsheets only and set it for only those Columns. BTW: I didn't suggest using Auto_Open, I suggest using the Workbook_SheetActivate.

Sorry Dave! I meant auto-open in the generic sense that it would automatically execute, not in the outdated and no longer recommended Auto_Open syntax!

Yes, you and RS are assuming correctly that I am wanting some columns to be wider than their data and others to be narrower than their titles, and the 'formatting' I am referring to that would be 'destroyed' is merely the appearance of the printed page, which is very important to my users.

I do think the root problem is somehow in either the PC or Excel install -- that just puts it beyond my ability to repair.

I think in the future I'll just have to incorporate specified column widths where they're important...

Thanks -