excel - 为 Excel 工作表运行多个带有私有宏的函数

Question

我正在尝试为 Excel 工作表键入代码，它允许选择多个下拉选项，隐藏基于单元格值的命令按钮，并且当工作簿关闭时它会清除某些内容。

我已经设法选择多个下拉选项并隐藏和取消隐藏命令按钮。但我不确定如何组合代码，以便在工作簿关闭时清除某些单元格值。我可以结合这两个宏吗？

Private Sub Worksheet_Change(ByVal Target As Range)

' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Dim strwork As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$5" Or Target.Address = "$C$9" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
'If user is selected, hide button. If admin is selected, enter password to see the button'
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
ActiveSheet.CommandButton1.Visible = False
If Range("A5") = "Admin" Then
     strwork = InputBox("Please enter Admin Password.")
     If strwork = "excel" Then
         ActiveSheet.CommandButton1.Visible = True
     Else
         MsgBox ("Incorrect Password")
         ActiveSheet.CommandButton1.Visible = False
         Range("A5") = "User"
     End If
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Clears the contents of the specified cells
Sheets("Sheet2").Range("B5:C5").ClearContents
Sheets("Sheet2").Range("D1").ClearContents
' Closes workbook without saving by telling Excel the workbook is already saved
ThisWorkbook.Saved = True
End Sub

标签： excelvba