如何使用 VBA 编辑表格标题 - Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25843473/
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 edit table headers with VBA - Excel
提问by Goos van den Bekerom
I Have a sheet that adds a table for each project I got in a list.
But I can't find the code to edit the table headers after the table is added.
There probably is an easy solution, but I can't manage to find one.
我有一张表格，为我在列表中得到的每个项目添加了一个表格。
但是我在添加表格后找不到编辑表格标题的代码。可能有一个简单的解决方案，但我找不到。
Sub getAllProjectsTest()
Dim totalSheet As Worksheet
Set totalSheet = ThisWorkbook.Sheets("Total Overview")
totalSheet.Range("A2:Z4000").Clear
For Each project In Sheets("Projects-Tasks").Range("Projects")
Dim LastRow As Long
LastRow = totalSheet.Cells(Rows.Count, 1).End(xlUp).Row
With totalSheet.Range("A" & LastRow).Offset(3, 0)
.Value = project
.Font.Size = 16
.Font.Bold = True
End With
Dim ListStart As String
ListStart = totalSheet.Range("A" & LastRow).Offset(4, 0).Address
Dim TableWidth As String
TableWidth = totalSheet.Range("A" & LastRow).Offset(4, 3).Address
totalSheet.ListObjects.Add(xlSrcRange, Range(ListStart & ":" & TableWidth), , xlYes).Name = "Table" & project
Range(ListStart & ":" & TableWidth).HorizontalAlignment = xlCenter
Next project
End Sub
采纳答案by Siddharth Rout
It becomes easier if you work with objects. See this example
如果您使用对象，它会变得更容易。看这个例子
'
'~~> Rest of the code
'
Dim tbl As ListObject
Set tbl = totalSheet.ListObjects.Add(xlSrcRange, _
Range(ListStart & ":" & TableWidth), , xlYes)
With tbl
.Name = "Table" & project
Debug.Print .HeaderRowRange.Address
End With
'
'~~> Rest of the code
'
回答by John
Another more fun way to do this.
另一种更有趣的方式来做到这一点。
Sub trimHeaders()
Dim wlistobj As ListObject
Dim wlistcol As ListColumn
Set wlistobj = ThisWorkbook.Sheets(1).ListObjects(1)
For Each wlistcol In wlistobj.ListColumns
wlistobj.HeaderRowRange.Cells(wlistcol.DataBodyRange.Column) = Trim(wlistobj.HeaderRowRange.Cells(wlistcol.DataBodyRange.Column))
Next wlistcol
End Sub
