Auto-adding/removing comment & populating current date

[fijibound]

Hi all. I should probably start this by saying I suspect what I want to achieve requires VBA, something of which I have zero experience/knowledge, so might also need some pointers where to place any relevant code.

Currently I have an Excel doc, and I have one column setup with some simply conditional formatting. If I type 'y' unto one of these cells, it automatically turns the foreground and background of the cell red (I'm using it a bit like an on/off button to tell me this is currently in progress).

However I also need to know the date that I started the process. At the moment I am manually entering this information into a neighbouring notes field. However this slows down the process, plus as my sheet grows I'm trying to eliminate any columns I can to keep the size down.

So what I would ideally like is that when I enter any text into one of these cells, a comment will automatically be added to that cell showing the date I entered text into the cell. Then when I delete all text from the cell the comment is automatically removed.

Does anybody know a way I can achieve this?

Thanks for any help.

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim d As String
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      d = Now
      Target.ClearComments
      If Target.Value <> "" Then Target.AddComment d
   End If
End Sub

This needs to go in the sheet module.
Right click the sheet tab > View code > paste the above into the window that opens up.
This will currently work on col A

 

[fijibound]

Hi Fluff. Thank you for your reply.

I did have further questions but right now I'm not able to get the above working, but it may be me doing something wrong through inexperience.

So this is what I've done:

1.) Created new workbook for testing.
2.) Made quick headers for columns A, B and C.
3.) Used Alt-F11 to bring up the VBA editor.
4.) Right clicked on the related worksheet and selected Insert -> Module.
5.) Copy-pasted your above suggestion into the new module window which appeared.
6.) Saved the document as a macro-enabled workbook.
7.) Tested entering text into a cell in column A.
8.) Currently nothing happens - no comment is added or any other change which I can see.

Also not sure how much difference it makes but neglected to mention I'm using Excel 2016.

 

[fijibound]

Sorry ignore above I neglected to read your instructions correctly!

Okay I have it working now as you've described. I guess my follow up questions are:

1.) Is it possible to populate just the date, without the time? Not a big deal if not haha I'm just a bit fussy and like things tidy.
2.) Which part of the code is dictating that this is specific to column A? In my case I need this in column C but I'd like to know how to change it for future use too.
3.) Is there a way to exclude the top row (column header) from this?

 

[fijibound]

I've just realised my own answer to question 2 with a little common sense.

Question 3 is now the important(ish) one and question 1 would be a 'nice to have'.

 

[Fluff]

OK try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim d As String
   If Target.CountLarge > 1 Then Exit Sub
   [COLOR=#0000ff]If Target.Row = 1 Then Exit Sub[/COLOR]
   If Target.Column = [COLOR=#ff0000]3 [/COLOR]Then
      d = Date
      Target.ClearComments
      If Target.Value <> "" Then Target.AddComment d
   End If
End Sub

The value in red specifies which column this will work on. So originally it was 1 =col A, now it's 3 =col C.
The line in blue says if the target (ie the cell that has changed) is in row 1 then exit.

 

[fijibound]

Just tested and that works a charm. Thank you so much Fluff! Also for the explanation it's nice to understand (at least somewhat) what you are reading.


I had a bit more.. I do tend to alter most of my Excel sheets over time to better fit purpose, but as I'm getting advice from you I'm trying to hit all the future nails on the head in advance to save return visits for more info on the same subject.


1.) If I were to add data validation to the to the cells in the column, so instead I had a dropdown offering me text choices, I assume this would not break the code?


2.) I have another column I may wish to do similar with in the near future. However this one differs in that the cell will always be populated with 'yes', 'no' or 'in prog'. In the case of this column I would want a comment with date populated added when I changed the cell to 'in prog'. When the column reads 'no' (always it's initial status) I do not need a comment. When the cell reads 'yes' (always it's final status) - well I'm a bit undecided on that one but either it will be no comment/no date, or alternatively update the comment to the new date when I have changed the cell to read 'yes' (from it's previous status of 'in prog'). What code would I require in this second example?


Sorry to take up your time with this, and thank you again for all your help so far.

 

[Fluff]

1) Yes, DV is fine, but you would not be able to use form or ActiveX controls.
2) try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim d As String
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Row = 1 Then Exit Sub
   d = Date
   If Target.Column = 3 Then
      Target.ClearComments
      If Target.Value <> "" Then Target.AddComment d
   ElseIf Target.Column = 5 Then
      Target.ClearComments
      If LCase(Target.Value) = "in prog" Then
         Target.AddComment = d
     [COLOR=#0000ff] ElseIf LCase(Target.Value) = "yes" Then
         Target.AddComment = d[/COLOR]
      End If
   End If
End Sub

If you want to remove the comment if "yes" then remove the lines in blue

 

[fijibound]

Tried the above on my test sheet. I have noted you're using column 3 and column 5 so made sure to test with these columns.

The first bit of code applying to column 3 is still working as expected.

With column 5 if I enter 'in prog' or 'yes' I am receiving "Run-time error 438. Object doesn't support this property or method". A comment is added to the cell still, but it is an empty comment (no date or any other text).

If I click 'debug' it highlights the line which reads:
Target.AddComment = d
(Either under the if for 'in prog' or the elseif for 'yes' depending which one I attempted to enter into the cell).

 

[Fluff]

They should both be

         Target.AddComment d

Remove the = sign