Stop column resizing madness

[drpdrpdrp]

I do not like Excel's default behavior of:

• expanding a column width if a large number is typed/returned for the first time
• not resizing if a large number is typed/returned, if it has been already resized
• not resizing if a large character string is entered ("spill" contents)

Seems like a workaround would be to resize all columns (for the "first time") before working in a sheet. I would expect something like:

Sub columnWidth()
    Range("A:XFD").columnWidth = 8.5
End Sub

to work, but it does not. Any suggestions?
To be clear, what I am trying to achieve is: a vba sub that I can run before I start to work on a sheet that will prevent any *automatic* width and height adjustments

 

[aaewalsh]

Try this

Sub PreventAutomaticAdjustments()
    With Application
        .AutomationSecurity = msoAutomationSecurityForceDisable
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
End Sub

Private Sub Workbook_Open()
    Call PreventAutomaticAdjustments
End Sub

 

[Fluff]

It might be a good idea if you explain exactly what that code does, as it could easily cause problems.

 

[drpdrpdrp]

Try this

Sub PreventAutomaticAdjustments()
    With Application
        .AutomationSecurity = msoAutomationSecurityForceDisable
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
End Sub

Private Sub Workbook_Open()
    Call PreventAutomaticAdjustments
End Sub

Thanks. Any chance you could explain why this code will prevent the column width to resize based on cell contents?

 

[Fluff]

It won't, but it could stop your workbooks from working correctly.

 

[drpdrpdrp]

It won't, but it could stop your workbooks from working correctly.

@Fluff Any suggestions on this one?

 

[Fluff]

Fraid not. I not sure it's even possible.