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

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


On Tue, 19 Oct 2010 11:38:31 -0700 (PDT), al <(E-Mail Removed)> wrote:

>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

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.


On Oct 19, 11:59*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> 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 intothat
> 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
>

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

On Wed, 20 Oct 2010 08:07:53 -0700 (PDT), al <(E-Mail Removed)> wrote:

>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.
>
>
>On Oct 19, 11:59*pm, Gord Dibben <phnor...@shaw.ca> wrote:
>> 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
>>