Place date stamp in cell comment using VBA

[robertdodson1979]

My goal on the form is simply that when you click the check-box, it will highlight the cell green and place the date in the cell comment, and then clear the comment and color when unchecked.

Thanks to resources such as this one, I have been successful with the exception that I can only place text in the comment, but not a date stamp or even a reference to a hidden cell. I accomplished a date stamp command on a button with "Sheets("Sheet1").Range("Q5").Value = Date" but am unable to do this in a comment.

Below is what I have currently. I know it is not clean to look at, but that is how I found it and didn't want to create more variables for troubleshooting

---

Private Sub CheckBox1_Change()
Sheets("Sheet1").Range("Q4").ClearComments
Sheets("Sheet1").Range("Q4").Interior.Color = RGB(255, 255, 255)
End Sub


Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Range("Q4").ClearComments
Sheets("Sheet1").Range("Q4").AddComment ("Hello")
Sheets("Sheet1").Range("Q4").Comment.Visible = False
Sheets("Sheet1").Range("Q4").Interior.Color = RGB(0, 255, 0)
End If
End Sub

---

I swear that I searched around for the answer to this before posting. I came across a couple results that seemed promising, but the code seemed to have a different syntax and I couldn't make it work with my VBA newbie skill level (started yesterday).


Thanks in advance for your time!

 

[Gavin T]

You can convert the date to a string before entering it as acomment by using the CStr function as follows:

Sheets("Sheet1").Range("Q4").AddComment (CStr(Date))

 

[Gavin T]

Here is your code cleaned up a bit.

 Private Sub CheckBox1_Change()

    With Sheets("Sheet1").Range("Q4")
        If CheckBox1.Value = True Then
            .ClearComments
            .AddComment (CStr(Date))
            .Comment.Visible = False
            .Interior.Color = RGB(0, 255, 0)
        Else
            .ClearComments
            .Interior.Color = xlNone
        End If
    End With

 End Sub

EDIT: Yay! My 300^th post.

 

[robertdodson1979]

That worked great!

Thanks, Gavin T, for the quick response and the bonus clean code. Congrats on post #300!



I was planning to do a sheet with around 228-261 of these boxes.

As a follow-up question...is there a simpler way to mass-produce the check-boxes tied to the locations (i.e B2, C2, D2, etc.) or do I just need to suck it up and plow through it this time?

 

[Gavin T]

I'm not so familiar with building forms programmatically but here's what I have. I have not rigorously tested but see what you can do with it.

[color=darkblue]Sub[/color] CreateForm()

    [color=darkblue]Dim[/color] cbxNew [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] objForm [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] CodeLine [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]Set[/color] objForm = ThisWorkbook.VBProject.VBComponents.Add(3)

    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] 20
        [color=darkblue]Set[/color] cbxNew = objForm.designer.Controls.Add("Forms.CheckBox.1")
        objForm.Properties("Height") = 12 * i + 40
        [color=darkblue]With[/color] cbxNew
            .Name = "CheckBox" & i
            .Caption = "Cell A" & i
            .Top = 12 * i
            .Left = 10
            [color=darkblue]With[/color] objForm.codemodule
                l = i * 13 + 1
                .insertlines l, "Private Sub " & cbxNew.Name & "_Change()"
                .insertlines l, "With Sheets(""Sheet1"").Range(""A" & i & """)"
                .insertlines l, "If CheckBox1.Value = True Then"
                .insertlines l, ".ClearComments"
                .insertlines l, ".AddComment (CStr(Date))"
                .insertlines l, ".Comment.Visible = False"
                .insertlines l, ".Interior.Color = RGB(0, 255, 0)"
                .insertlines l, "Else"
                .insertlines l, ".ClearComments"
                .insertlines l, ".Interior.Color = xlNone"
                .insertlines l, "End If"
                .insertlines l, "End With"
                .insertlines l, "End Sub"
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color]

    objForm.Properties("ShowModal").Value = [color=darkblue]False[/color]
End [color=darkblue]Sub[/color]

Good luck!