Unwanted Change of Column Width

[MorganDavis]

Lately the following has happened to several excel sheets I use: Due to reasons unknown to me, all the columns have suddenly decreased in width, making the sheet look all cramped up. I use sheets that have quite extensive layout formatting and many macro buttons. Whenever this happens I have to adjust all the columns ánd all the buttons to their proper size again, which is often pretty tedious work. And then the same thing happens after a couple of days!

 

[kskinne]

hello and welcome to the board. for your macro buttons, you should right-click on each button, select 'format control', and select the 'properties' tab. then you can set each one to not move or size every time a column width changes. as far as your column widths are concerned, do you have any code in the file that may be responsible for adjusting your column widths? otherwise, if you cannot determine the cause of the columns adjusting, and stop it, you could insert some vba code to set your columns to a predetermined width if you have a certain width in mind for each column. i would tie this to a workbook_open event, so that the columns are adjusted to the width you want them every time you open your file. the code would be something like this for each column:

Columns("A:A").ColumnWidth = 8.43

substituting your columns and column widths in this code snippet.

HTH
kevin

 

[Andrew Poulsom]

I have found this happens when someone else uses the workbook and they have different regional settings. It happens a lot on spreadsheets I send to the USA where they use inches instead of centimetres.

Does this fit your scenario?

I don't know a solution, other than to match the regional settings.

 

[MorganDavis]

Both Kevin and Andrew, thank you for taking the time to answer my question, I very much appreciate it!

Thanks for the tip about changing my button sizes, I think I new that little trick already though; the biggest problem is that I sometimes have to resize about 80 buttons piece by piece, which you might agree is a gutwrenchingly tedious job.

I will try and include the little piece of VBA code in my sheets, and I hope it will solve my problem.

As for the regional settings, my problems have occured with sheets that have never left the internal network, but perhaps there is a computer somewhere in our intranet that measures in inches thus ruining it all for the centimeter-machines. I will have to investigate into that...

Thanks and I will report back with the results!

Morgan