第三步
单击“工具”--“宏”--“Visual Basic编辑器”,在代码区分别在以下四个事件中输入相应的代码。如图二所示。
以下是四个事件的相应代码。
Private Sub DTPicker1_CloseUp() '禁用事件,在将DTP控件的值更新到单元格时,防止Worksheet_Change被误激活 Application.EnableEvents = False ActiveCell.Value = Me.DTPicker1.Value Me.DTPicker1.Visible = False '启用事件 Application.EnableEvents = TrueEnd Sub
Private Sub Worksheet_Change(ByVal Target As Range)'判断是否只选中了单个单元格 If Target.Count = 1 Then '如果删除第三列的单元格内容,则隐藏DTP控件 If Target.Column = 3 And Target = "" Then Me.DTPicker1.Visible = False End If End IfEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Count = 1 Then Application.EnableEvents = False If Target.Column = 3 Then With Me.DTPicker1 .Visible = True '调整DTP控件的位置,使其显示在当前单元格之中 .Top = Target.Top .Left = Target.Left If Target <> "" Then '如果当前单元格已有内容,则设置DTP控件初始值为当前单元格日期,否则为系统当前日期 .Value = Target.Value Else .Value = Date End If End With Else Me.DTPicker1.Visible = False End If Application.EnableEvents = True End IfEnd Sub
Private Sub Workbook_Open() '调整DIP控件的大小 With Sheet1.DTPicker1 .Height = Sheet1.Cells(1, 3).Height .Width = Sheet1.Cells(1, 3).Width + 18 .Visible = True End WithEnd Sub