Cell containing formula won't resize culumn width

[Guest]

In a column for which I have turned on the autofit column width, I have a series of numbers with a sum formula at the bottom of the series. The column width will automatically resize for numbers entered that are wider than the current width; however, if the result of the sum formula is wider than the current width, the column will not automatically resize to accomodate the width of the resulting sum. Is there a way to fix this without manually having to resize the column? The worksheet is protected, so it is a pain to unprotect, change and reprotect.

 

[Dave Peterson]

You could have a macro do it for you with each calculation:

rightclick on the worksheet tab that should have this behavior and select view
code.

paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()

Const PWD As String = "hi"
With Me
.Unprotect Password:=PWD
.UsedRange.Columns.AutoFit
'.Columns("c:e").AutoFit
.Protect Password:=PWD
End With

End Sub

You could do all the columns or just a selected few.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

Don't forget to lock the VBA Project, too. Else you'll have inquisitive types
looking at your code and seeing the password.

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.