CODEWITHSUNDEEP
vbaexceldate
Vbasic4now
Vbasic4now

Reputation: 589

Excel entering the date time into a cell when another cell has a value entered into it

I am trying to create a column that acts as a unique key value for each row, by adding the Now() value when the last column has had its data entered into it. I have created a sub that checks the range of cells for one to be selected and have a value in it and if it is, add the Now() value to another column to act as a unique identifier for that row. This is the code that i have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As range)

    'Check to see if the click/selected cell is in column H
    If Not Intersect(Target, range("H:H")) Is Nothing Then

        'Make sure just one cell is selected and it isnt empty
        If Target.Cells.Value <> "" And Target.Row > 7 And Target.Row <= 20 Then

            'Update the "KeyA" value
            range("A" & Target.Row).Value = Now()
        End If

    End If

End Sub

Unfortunately this only adds the Now() value to the key column if the selected cell already has a value in it so if I click the empty cell and then add the value, it does not add Now() to the key column. How do I make the sub add the Now() value once an entry has been added to the selected cell with out having to re-select the cell after I have added a value to it?

Upvotes: 0

Views: 180

Answers (1)

Dave
Dave

Reputation: 4356

Use Worksheet_Change event instead of Worksheet_SelectionChange.

Change will fire when the value in the target cell is amended, whereas SelectionChange fires when you select a different cell.

Upvotes: 1

Related Questions