automatic date entry in XL cell..

[al]

Hi,

This problem might be a complicated one and I am not sure if XL can do
this however I will solicit any feedback.

I have some pre-defined data in the cell that I am selecting via Data
Validation method. Is there a way to automatically insert in comment
field the date that particular entry was selected. For e.g. I am
selecting "Voltage Failure" from the drop down list, a comment of 19-
oct-2010 should automatically appear on the comment field of that
particular cell.

Again not sure if XL can do this or you need a VB code?

Thanks for the feedback

 

[Gord Dibben]

You will need VBA

You could use sheet event code such as...................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strDate As String
Dim cmt As Comment
strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$H$1" And Target.Value <> "" Then
cmt.Delete
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10)
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If
With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range "$H$1" and/or strDate format to suit.

Post back if you need a running log.


Gord Dibben MS Excel MVP

 

[al]

Hi Gord,

I copied your code into the code module and I modified the $H$1
reference to where my data is referenced to, I saved the worksheet and
opened it again with Macro content enabled (xlsm format), however it
did not do inserted the date into the selected cell. Can I email you
the file and you can check it out if I am doing it right?

thanks for your help.

 

[Gord Dibben]

The date/time should not be inserted into the cell.

The cell should receive a Comment with date/time in it.

I will look at your workbook.

change phnorton to gorddibb


Gord