Home >Software Tutorial >Office Software >How to use the date control to select and insert a date in a certain cell in Excel 2003
In order to help those players who have not successfully passed the level, let us learn about the specific puzzle solving methods. For Excel2007, we can use VBA to solve the problem. The following are the specific steps:
1. Open Excel, click the "Development Tools" tab, in the "Controls" group, find and click "Insert", in the drop-down list, click "Other Controls" in the lower right corner, as shown below:
2. In the pop-up "Other Controls" dialog box, drag the scroll bar to the bottom, select "Calendar Control 12.0" with the left mouse button, and click "OK"
3. Return to the worksheet, drag out a calendar control with the left mouse button, and adjust the size and position, as shown below:
4. Double-click the "Calendar Control" with the left mouse button to bring up the VBA editor, copy and paste the following code into it
Private Sub Calendar1_Click()
ActiveCell = Format(Calendar1.Value, "yyyy-mm-dd")
Me.Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Row > 1 Then
With Me.Calendar1
.Visible = True
.Top = Target.Top Target.Height
.Left = Target.Left Target.Width
.Value = Date
End With
Else
Me.Calendar1.Visible = False
End If
Else
Me.Calendar1.Visible = False
End If
End Sub
Where "If Target.Column = 1 Then
If Target.Row > 1 Then "Click on the first column, column A, except for the first row, to pop up the calendar control. You can make changes as needed, and then close the VBA code editor and return to the worksheet
5. Click the left mouse button on A2 to pop up the "Calendar Control". Click the date to be entered on the "Control" as needed
Private Sub DTPicker1_Change()
ActiveCell.Value = DTPicker1.Value
DTPicker1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.DTPicker1
If Target.count=1 then
If Target.Column = 1 And Target.Column =5 Then
.Visible = True
.Width = Target.Width 15
.Left = Target.Left
.Top = Target.Top
.Height = Target.Height
Else
.Visible = False
End If
else
.Visible = False
end if
End With
End Sub
After many experiments, using this code, I can use the calendar control on columns 1 and 2 at the same time, and clicking other columns will not appear.
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
Me.Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column 1 Then (meaning less than and including 2 columns and not including the first row)
Me.Calendar1.Visible = True
Else
Me.Calendar1.Visible = False (clicking other columns will not appear)
End If
End Sub
The above is the detailed content of How to use the date control to select and insert a date in a certain cell in Excel 2003. For more information, please follow other related articles on the PHP Chinese website!