New to the forum and new to VBA

[SirReal31]

Hi,

I am having the fun task of converting some Google sheets to Excel sheets, the google sheets have some javascript with that I need to change to VBA to enable the functionality in the final excel sheet.

I am looking to do some thing simple really, when someone enters a value into Col A I want a time stamp to appear in Col M, that part is fine, I also want to have in Col J a comment added with a time stamp when something is added to the cell. I have the first part sorted but I am trying to do the second.

The code issue is dealing with a range for the entry in ColJ, I currently have a set cell J7 that works but I want it to work for all of ColJ.

You can see i initially wanted to repeat the action of the Target usage but that doesn't work, also I needed to change the action from adding full text to a into a comment.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler


'Add Timestamp to ColM
If Target.Column = 1 And Target.Value <> "" Then
    Application.EnableEvents = False
    Target.Offset(0, 12) = Format(Now(), "dd-mmm-yyyy hh:mm:ss")
    Application.EnableEvents = True
End If


'Add Timestamp to ColJ - Needs fixing
''If Target.Column = 10 And Target.Value <> "" Then
'    Application.EnableEvents = False


'Here is where I need to add and integrate the AddComment
'    Target.Offset(0, 0) = ActiveCell.AddComment.Text(myTime)
'    Application.EnableEvents = True
'End If


Dim myTime As Variant
myTime = Format(Now(), "dddd,d-mmm-yyyy hh:mm:ss")
Range("J7").Select
ActiveCell.AddComment.Text (myTime)


Handler:


End Sub

Could you please give me advice or show me what I should do, once i see trhat correct options I should be able to understand for reuse.

Thanks

 

[Kamolga]

I am not sure to completely understand your issue. This will get your time stamp as comment in column j (same row) when you enter a value in column A

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler


[COLOR=#008000]'Add Timestamp to ColM[/COLOR]
If Target.Column = 1 And Target.Value <> "" Then
    Application.EnableEvents = False
    Target.Offset(0, 12) = Format(Now(), "dd-mmm-yyyy hh:mm:ss")
    Application.EnableEvents = True
End If


[COLOR=#008000]'Add Time stamp as comment in col 10 (J)[/COLOR]
Dim myTime As Variant
myTime = Format(Now(), "dddd,d-mmm-yyyy hh:mm:ss")
Cells(Target.Row, 10).AddComment.Text (myTime)


[COLOR=#008000]'Autofit comment[/COLOR]
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
Next


Handler:


End Sub

 

[SirReal31]

Thank you for that, however I am only wanting the timestamp in the comment when the cell value changes in Col J, so two actions, once when a Cell in Col A changes and then a different trigger when entry in Cell in Col J, can that all be don in the single Sub or should it be split to two so there is no crosstalk?

 

[Kamolga]

Thank you for that, however I am only wanting the timestamp in the comment when the cell value changes in Col J, so two actions, once when a Cell in Col A changes and then a different trigger when entry in Cell in Col J, can that all be don in the single Sub or should it be split to two so there is no crosstalk?

You can only have one worksheet_Change event by worsheet but you can assign conditions to target:
-if target column is A then do this
-if target column is J then do that
like in code below

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
Dim myTime As Variant
myTime = Format(Now(), "dddd,d-mmm-yyyy hh:mm:ss")

[COLOR=#008000]'Add Timestamp to ColM when value change in Column A[/COLOR]
If Target.Column = 1 And Target.Value <> "" Then
   Cells(Target.Row, "M") = Format(Now(), "dd-mmm-yyyy hh:mm:ss")
End If

[COLOR=#008000]'Add Time stamp as comment in col 10 (J) when value change in column J[/COLOR]
If Target.Column = 10 And Target.Value <> "" Then
  Cells(Target.Row, Target.Column).AddComment.Text (myTime)
End If

[COLOR=#008000]'Autofit comment[/COLOR]
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
Next

Handler:
End Sub

 

[SirReal31]

Perfect, that works great, now with the structure I can see how to utilize this for different features, thanks that was a great answer.

 

[Kamolga]

You are welcome, thanks for the feedback!