New to the forum and new to VBA

SirReal31

New Member
Joined
Nov 16, 2018
Messages
3
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.

Code:
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
Code:
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
 
Last edited:
Upvote 0
Perfect, that works great, now with the structure I can see how to utilize this for different features, thanks that was a great answer.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top