vba 如何在文本输入期间自动调整 Excel 中的列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2048295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to auto-size column-width in Excel during text entry
提问by ChrisA
I usually try to avoid VBA in Excel, but it would be convenient to be able to type text into a cell, and have its column get wider or narrower to accommodate the text remaining as it's entered or deleted.
我通常会尽量避免在 Excel 中使用 VBA，但是能够在单元格中输入文本并使其列变宽或变窄以容纳输入或删除时剩余的文本会很方便。
This would be subject, of course, to the lengths of the text in the other cells in the column.
当然，这取决于列中其他单元格中的文本长度。
'Auto-fit as you type', I guess you might call it.
“键入时自动调整”，我想您可能会这么称呼它。
Is there an easy way to do this in a suitable handler?
有没有一种简单的方法可以在合适的处理程序中做到这一点？
回答by Fink
I'm not sure if there is a way to do it while your typing. I think excel generally stretches the cell view to display all the text before it fires the worksheet_change event.
我不确定在您打字时是否有办法做到这一点。我认为 excel 通常会在触发 worksheet_change 事件之前拉伸单元格视图以显示所有文本。
This code will resize the column after you have changed and moved the target to a new range. Place it in the worksheet module.
在您更改目标并将目标移动到新范围后，此代码将调整列的大小。将其放在工作表模块中。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextTarget As Range
Set nextTarget = Range(Selection.Address) 'store the next range the user selects
Target.Columns.Select 'autofit requires columns to be selected
Target.Columns.AutoFit
nextTarget.Select
End Sub
If your just looking to do it for a particular column you would need to check the target column like this:
如果您只是想为特定列执行此操作，则需要像这样检查目标列：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextTarget As Range
Set nextTarget = Range(Selection.Address) 'store the next range the user selects
If Target.Column = 1 Then
Target.Columns.Select 'autofit requires columns to be selected
Target.Columns.AutoFit
nextTarget.Select
End If
End Sub
回答by Sathish
I cannot think of a way to do what you ask for but something very close to your need. In modern versions of Excel (2010+, I don't know about the 2007 version) you could use the following macro to resize your column to fit data as soon you finished entering data in a cell.
我想不出一种方法来满足您的要求，但非常接近您的需求。在 Excel 的现代版本（2010+，我不知道 2007 版本）中，您可以使用以下宏调整列大小以在单元格中输入数据后立即适应数据。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
ActiveSheet.Columns.AutoFit
End Sub
Put the macro in ThisWorkbook module
将宏放在 ThisWorkbook 模块中
回答by aym has
This will automatically fit columns width
这将自动适合列宽
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns.AutoFit
End Sub
回答by Reverus
I just tried the previous two answers on a sheet and they didn't do anything, idk if the "ByVal Sh" is the problem?? was it a typo?
我只是在一张纸上尝试了前两个答案，但他们什么也没做，我知道“ByVal Sh”是否是问题所在？？这是一个错字吗？
Anyhow, here is my answer, checked it and it works:
无论如何，这是我的答案，检查它并且它有效：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target Is Nothing Then
Exit Sub
Else
With Target
.Columns.Select
.Columns.AutoFit
End With
End If
End Sub
-.Reverus.
-.Reverus。
