Mystery Autofit - widths automatically adjusting

[9tanstaafl9]

I'm pretty sure that I have seen a workbook before, where the column widths adjusted themselves automatically whenever the value in a column got large enough that it required an adjustment to avoid seeing #######.

And when I say automatically, I don't mean that the column was adjusted during the intentional running of a macro. It's possible there was something going on automatically that I didn't know about, but no buttons were being pushed.

Of course, I could be getting senile and I'm just remembering something that didn't happen. It's also possible that this is just a setting I'm unaware of since I've been using Excel for so long that I missed it when a new feature popped up.

If you happen to know if this is possible, or impossible, could you please tell me. If possible tell me how. Thanks!

 

[bigmyk2k]

This seems familiar to me too, but I can't figure it out right now.
The best solution I can find is to select the whole sheet (ctrl+A, or click in the upper left above the first row, and to the left of the first column) and select Home >> Cells >> Format >> Auto Fit Column Width

 

[9tanstaafl9]

Thanks, that would be great if the sheet was for me, but I'm giving it to newbies who couldn't handle that. I can make a macro to do it, I just thought it would be nice if they didn't have to click a button.

 

[Ray Huber]

What you need to have is a macro that autofits the columns in the Changed rangs and put it into the worksheet_change event so whenever someone changes anything in the sheet it updates the columns affected. I don't know the syntax off the top of my head and I'm on my phone so I can't look it up, but I should be able to figure it out later.

Hope that points you in the right direction!

-Ray

 

[9tanstaafl9]

Thank you! That's might be what I needed. I didn't think of the worksheet_change event. That's not something I'm familiar with. I found this online:

<code>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Columns.AutoFit
End Sub</code>

That might work, but I'm not sure. I need to connect to their data to find out. My issue is that the worksheet only changes when queries on OTHER sheets get updated. So I'm not sure if this will actually qualify as a change event. They aren't required to actually click anywhere on this sheet.

 

[9tanstaafl9]

I spoke too soon. That does not work. It only autofits the column that I double click in. I need something that fixes all the columns. I'm a little confused as to why this code doesn't do that. Also, it won't work on a protected sheet.

No worries, I'll just add a button. I thought I'd ask in case this was a simple thing to do that I was simply unaware of.

 

[Ray Huber]

The Worksheet_SheetChange only activated when you switch to a different sheet, and is therefore not what you want. Also, You definitely don't want a macro that turns screen updating off and then doesn't turn it on again. The following macro should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Columns.AutoFit
End Sub

Let me know if that works!

-Ray