excel - 我想修改代码，以便在打开工作簿后立即运行宏

Question

该宏是关于锁定包含所有日期的列，除了具有今天日期的列。我在每张纸上都进行了编码，并且在所有纸上都完全相同。宏在任何单元格中的数据发生更改时运行。但是我希望在打开工作簿时运行宏。

我试图在“本工作簿”中对其进行编码，但我不知道该怎么做。我也尝试在“模块”中做到这一点，但做不到。

 Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  'vps
  Dim x As Long
  x = 7
  ThisWorkbook.ActiveSheet.Unprotect Password:="123456"
  ThisWorkbook.ActiveSheet.Cells.Locked = False
  Do Until IsEmpty(Cells(5, x))
    If Cells(5, x) <> Date Then
      Columns(x).Locked = True
    End If
    x = x + 1
  Loop
  ThisWorkbook.ActiveSheet.Protect Password:="123456"
  End Sub

我希望宏在打开工作簿时运行，而不仅仅是在单元格中的数据更改时运行。

Answer 1

打开工作簿时锁定单元格

特征

要在工作簿打开时运行以下(或任何)宏，您有
使用其 Workbook_Open 事件。在 VBA 中双击 ThisWorkbook。在
第一个下拉菜单选择 Workbook，第二个下拉菜单选择 Open。

对于包含此宏的工作簿中的每个工作表，取消保护
它并解锁所有单元格。然后检查日期的指定行范围
并锁定未找到今天日期的整个列的单元格。
最后保护工作表。

此外，内部颜色在单元格中会有所不同
找到今天的日期。

链接

Workbook Download (Dropbox)

编码

标准模块 例如 Module1

Sub ProtectPrevious()

    Const cRow As Long = 5        ' Date Row Number
    Const cFirstC As Variant = 7  ' First Column Letter/Number e.g. 7 or "G"
    Const cToday As Long = 6      ' Today Cell ColorIndex e.g. 6 is Yellow
    Const cDays As Long = 15      ' Other Days ColorIndex e.g. 15 is some Gray

    Dim ws As Worksheet   ' Current Worksheet
    Dim LastC As Long     ' Last Column Number
    Dim j As Integer      ' Column Counter

    For Each ws In ThisWorkbook.Worksheets
        With ws
            ' Prepare for processing.
            .Unprotect Password:="123456"
            .Cells.Locked = False
            ' When there is no data in Date Row, continue with next worksheet.
            If .Rows(cRow).Find("*", .Cells(cRow, _
                    .Columns.Count), -4123, , 1) Is Nothing Then Exit For
            ' Calculate Last Column Number
            LastC = .Rows(cRow).Find("*", , -4123, , 1, 2).Column
            ' Remove formatting from other day(s) in Date Row.
            With .Range(.Cells(cRow, cFirstC), .Cells(cRow, LastC))
                .Interior.ColorIndex = cDays
            End With
            ' Loop through columns: from First Column to Last Column.
            For j = cFirstC To LastC
                If .Cells(cRow, j) <> Date Then
                    .Columns(j).Locked = True
                  Else
                    ' Apply formatting to 'today' in Date Row.
                    With .Cells(cRow, j)
                        .Interior.ColorIndex = cToday
                    End With
                End If
            Next
            .Protect Password:="123456"
        End With
    Next

End Sub

这个工作簿

Private Sub Workbook_Open()
    ProtectPrevious
End Sub