如何通过VBA宏取消Mac版Excel新增工作表的网格线

问题背景 #

现有一段用于新增行和工作表的VBA代码，需要在Mac版Excel中实现：每次通过该宏新增工作表后，自动隐藏该工作表的网格线。

实现方法 #

在宏代码中找到复制模板工作表的逻辑，捕获新生成的工作表对象，然后设置其DisplayGridlines属性为False即可。具体修改步骤如下：

• 新增变量存储复制后的模板工作表对象
• 在复制模板的语句中捕获该对象
• 添加代码设置网格线隐藏

修改后的完整代码 #

Sub AddRowAndSheet()
    On Error Resume Next
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow
    Dim newSheetName As String
    Dim newSheet As Worksheet
    Dim currentSheet As Worksheet
    Static sheetCounter As Integer
    Dim LastRow As Long
    Dim initialRowHeight As Double
    ' 新增变量存储复制后的模板工作表
    Dim copiedSheet As Worksheet
    
    Set currentSheet = ThisWorkbook.ActiveSheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("List of Assets")
    On Error GoTo 0
    
    If ws Is Nothing Then
        MsgBox "Worksheet 'List of Assets' not found!", vbExclamation
        Exit Sub
    End If
    
    On Error Resume Next
    Set tbl = ws.ListObjects("MyTable")
    On Error GoTo 0
    
    If tbl Is Nothing Then
        MsgBox "Table 'MyTable' not found in 'List of Assets'!", vbExclamation
        Exit Sub
    End If
    
    Set newRow = tbl.ListRows.Add
    
    If newRow Is Nothing Then
        MsgBox "Failed to add a new row to the table!", vbExclamation
        Exit Sub
    End If
    
    LastRow = tbl.ListRows.Count
    newRow.Range.Cells(1, 1).Value = "Asset " & LastRow
    
    initialRowHeight = tbl.ListRows(1).Range.RowHeight
    
    newRow.Range.RowHeight = initialRowHeight
    
    sheetCounter = sheetCounter + 1
    newSheetName = "Sheet" & sheetCounter
    
    On Error Resume Next
    Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("List of Assets"))
    newSheet.Name = newSheetName
    On Error GoTo 0
    
    If newSheet Is Nothing Then
        MsgBox "Failed to create a new sheet!", vbExclamation
        Exit Sub
    End If
    
    ' 修改复制模板的语句，捕获新生成的工作表对象
    On Error Resume Next
    Set copiedSheet = ThisWorkbook.Sheets("Template").Copy(Before:=newSheet)
    On Error GoTo 0
    
    If Err.Number <> 0 Then
        MsgBox "Failed to copy 'Template' sheet!", vbExclamation
        Exit Sub
    End If
    
    ' 设置复制后的工作表隐藏网格线
    If Not copiedSheet Is Nothing Then
        copiedSheet.DisplayGridlines = False
    End If
    
    newRow.Range.Cells(1, 2).Value = ThisWorkbook.Sheets("List of Assets").Range("M7").Value
    
    On Error Resume Next
    ThisWorkbook.Sheets("List of Assets").Range("M7").Copy
    On Error GoTo 0
    
    If Err.Number = 0 Then
        newRow.Range.Cells(1, 2).PasteSpecial Paste:=xlPasteFormats
        newRow.Range.Cells(1, 2).PasteSpecial Paste:=xlPasteValidation
    Else
        MsgBox "Failed to copy and paste data from 'List of Assets'!", vbExclamation
    End If
    
    currentSheet.Activate
End Sub

说明 #

• DisplayGridlines是Excel工作表的原生属性，在Mac版和Windows版Excel中均可正常使用，设置为False即可隐藏网格线
• 代码中特意捕获了复制后的模板工作表对象，确保对正确的目标工作表进行网格线设置

内容的提问来源于stack exchange，提问作者Ali Razak