Various input methods for today's date
Using Excel,
I think that there are many opportunities
to record the date you entered.
It is troublesome to manually input each time.
Here are a several ways to enter today's date.
First of all, needless to say,
but I will show how to enter Today's date by hand.
If you wanted to input 20th May,
you might enter "5/20" in the cell.
It is changed as this year's date.
The second way is to use the TODAY function.
When you enter "= TODAY ()" into the cell, today's date is displayed.
However, for example,
the date displayed after 3 days
is updated to the date after 3 days.
It can be used as a copy source of the date.
Since there are no argument for the function,
Please be careful not to forget "()".
The third way is to use the list and TODAY function together.
Enter "= TODAY ()" in cell A1,
Then the date of today will be displayed.
For being able to select the value of cell A1,
Create a drop-down list in cell A2.
Then you can choose today's date in the list.
This date will not be updated unless you select it again in the list.
How to create a list:
Click on [Data] - [Input rule] in that order.
The window shown in the next image is displayed.
Specify the type of input value as "list" and
the original value as the cell in which TODAY function was input.
In this example, specify cell A1 as the original value.
A list has been created in cell A2.
Press the downward pointing triangle to display the list.
Then choose today's date.
If it is not displayed as a date,
In [Cell formatting], select [Date format] as the date.
Also, if you set the list, you can delete the value of the cell,
can not enter values other than options.
The last way is to use VBA
When you enter a value in a cell
The date is automatically entered in column A by this method.
Specifically, we will use the Worksheet Event.
"Event" is about when
The value of the sheet was changed.
Or The seat became active.
Macros not associated with a particular sheet
is described in "Standard module".
On the other hand,
Worksheet Events is relevant only to specific worksheets.
It is not described in "Standard Module".
To run a macro using "Event"
Use "Event procedure".
To create an Event Procedure
First, in the VBE window
Double click on the worksheet whose event you want to use,
then the code window is opened.
Like the image below,
In the drop-down list on the left side of the code window
Select "Worksheet".
Next, in the drop-down list on the right side of the code window
Select "Change".
The "Worksheet_Change" event procedure has been created.
This is done when the worksheet values are changed.
In this event procedure,
Write a code to enter the date when the sheet's value is changed.
The automatically created Worksheet_SlectionChange event procedure,
Please delete it for not using it this time.
Code here
Private Sub Worksheet_Change(ByVal Target As Range)'When the value of a sheet other than row A is changed'Date is entered in column A If Target.Column <> 1 Then Cells(Target.Row, 1) = DateValue(Now) End If End Sub |
Result: By entering "123" in cell B1,
Today's date was entered in cell A1.
You can get the row number of the cell whose value was changed with "Target.Row",
the column number with "Target.Column"S.
| 固定リンク
| コメント (0)
| トラックバック (0)
