打开xlsm文件时在所有工作表中自动运行vba宏

Question

为什么不能在所有工作表中自动工作这个vba宏?

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
LastRow = [A65000].End(xlUp).Row
For cRow = 1 To LastRow
If Cells(cRow, 15) = "OnGoing" Then
Rows(cRow).Font.Bold = True
Rows(cRow).Font.Color = RGB(156, 204, 0)
End If
If Cells(cRow, 15) = "Modified" Then
Rows(cRow).Font.Bold = True
End If
Next cRow
Columns("A:O").EntireColumn.AutoFit
End With
Next ws
End Sub

我做错了什么?

Answer 1

创建一个名为auto_open的public sub来运行打开xlsm工作簿的代码-您构建的内容似乎与MS文档一致，但auto_open在项目模块中始终工作没有问题。

https://support.microsoft.com/en-us/office/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44

如果模块被触发(将msgbox放入验证)，那么可能是因为您没有使用完全合格的范围/单元格名称，所以您需要.在cells和rows前面

If .Cells(cRow, 15) = "OnGoing" Then
.Rows(cRow).Font.Bold = True
.Rows(cRow).Font.Color = RGB(156, 204, 0)
End If
If .Cells(cRow, 15) = "Modified" Then
.Rows(cRow).Font.Bold = True
End If

Answer 2

@freeflow你是指这个吗?

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
LastRow = ws.Cells(ws.Cells.Rows.Count, 1).End(xlUp).Row
For cRow = 1 To LastRow
If ws.Cells(cRow, 15) = "OnGoing" Then
ws.Rows(cRow).Font.Bold = True
ws.Rows(cRow).Font.Color = RGB(156, 204, 0)
End If
If ws.Cells(cRow, 15) = "Modified" Then
ws.Rows(cRow).Font.Bold = True
End If
Next cRow
ws.Columns("A:O").EntireColumn.AutoFit
End With
Next ws
End Sub

它正在处理activeworkingsheet。

Answer 3

您可以在执行计算之前尝试激活表单，因此您的代码应该看起来像

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.activate
LastRow = [A65000].End(xlUp).Row
For cRow = 1 To LastRow
If Cells(cRow, 15) = "OnGoing" Then
Rows(cRow).Font.Bold = True
Rows(cRow).Font.Color = RGB(156, 204, 0)
End If
If Cells(cRow, 15) = "Modified" Then
Rows(cRow).Font.Bold = True
End If
Next cRow
Columns("A:O").EntireColumn.AutoFit
End With
Next ws
End Sub