Force user to add a comment when certain cell changes

[deletedalien]

Hi guys, I've been working on this Code for about 2 weeks and cannot get around to making it work, i am not that good at coding in VBA so i really need your guys expertise.

So my final goal is to either have the user double click the cell to add a comment or better yet force the user to add a comment after the cell changes but only on certain cells...

Also i want to make the comment box Fancier because #skillz hahaha

So this is what i have for doubleclicking

Source: VBA Express : Excel - Add comments to cells by double clicking the cell.

Option Explicit
Public oldRange As Range
 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    On Error Resume Next
    Dim rng As Range
    Set rng = Target(1, 1)
     
    oldRange.Comment.Visible = False
     
    With rng
        If Not .Comment Is Nothing Then
            If .Comment.Visible = False Then
                .Comment.Visible = True
            Else
                .Comment.Visible = False
            End If
        End If
    End With
     
    Set oldRange = Target(1, 1)
End Sub
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    On Error Resume Next
    Dim cmtText As String
    Dim inputText As String
     
    If Target.Comment Is Nothing Then
        cmtText = inputbox("Favor de Agregar Comentario:", "Comentario")
        If cmtText = "" Then Exit Sub
        Target.AddComment Text:=cmtText
        Target.Comment.Visible = True
        Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
    Else
        If Target.Comment.Text <> "" Then
            inputText = inputbox("Favor de Agregar Comentario:", "Comentario")
            If inputText = "" Then Exit Sub
            cmtText = Target.Comment.Text & Chr(10) & inputText
        Else
            cmtText = inputbox("Enter info:", "Comment Info")
        End If
        Target.ClearComments
        Target.AddComment Text:=cmtText
        Target.Comment.Visible = True
        Target.Comment.ShapeRange.TextFrame.AutoSize = True 'Remove if you want to size it yourself
    
             
    End If
     
    Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub

When cell changes

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("Retvto")) Is Nothing Then


 
    With Target
        
        If Not IsEmpty(.Value) Then
             ' Jack Clears the comment.
            .ClearComments
             ' Jack Creates the comment.
            .AddComment (Application.UserName & "  " & Date & "  " & Time)
       
             ' Can have user name in top left by adding name between ""
            .Comment.Visible = True
            .Comment.Shape.Select
             
                    With Selection
                .ShapeRange.AutoShapeType = msoShapeBevel
                .ShapeRange.Fill.ForeColor.RGB = RGB(82, 204, 100)
                .Font.Size = 8
                .Font.ColorIndex = 3
                
         End With
        Else: .ClearComments
        End If
       
    End With


        End If
Application.DisplayCommentIndicator = xlCommentIndicatorOnly


Reasonbox.Show


End Sub

(reasonbox code)

Private Sub CommandButton1_Click()





ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=(Application.UserName & " " & Date _
& " " & Time & vbNewLine & Vtoreason.Value)


End Sub


This last code is not really working as it should tho

ANY help would be appreciated.