VBA Code for pasting today's date into a text box

[kristiknyc]

Hi everyone,

I'm working on a "stamp" in excel that I'd like to have a combination of consistent text (Sketch Updated: ) and today's date (per #1 below). I've written the macro for the text and formatting for this text box with hard keyed text only, but am having trouble incorporating a way to paste today's date (per #2 below).

text box stamps.PNG
Here's the code I have so far for option 2 above:

Sub SketchUpdatedDate()
'
' SketchUpdatedDate Macro
'


'
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 51.75, 67.5, 228, _
        33).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Sketch Updated: TODAY DATE"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 26).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 26).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange.ScaleWidth 0.7368421053, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.5909090909, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Line.Visible = msoFalse
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 26).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
    Range("D10").Select
End Sub

I tried linking the box to a cell within the sheet with the date, per the below, but this prevented me from inserting any other hard keyed text:
text box.jpg
Any help is much appreciated!!

[snb]

Create a textbox in Design mode,

Private Sub Workbook_Open()
    Sheet1.textbox1 = "sketch update: " & Date
End Sub

[kristiknyc]

Create a textbox in Design mode,

Private Sub Workbook_Open()
    Sheet1.textbox1 = "sketch update: " & Date
End Sub

Thank you! Do you know if it's possible to format these text boxes in the same way in VBA (i.e. no outline, transparent fill, etc.)? The formatting options become greyed out in the main excel dialog so wasn't sure

[snb]

Please, do not quote.

It depends on which kind of textBox you use.
Post a sample file to illustrate your question(s).

[SamT]

 Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Sketch Updated: " & format(Date. "mm/dd/yyyy")

If you need an Either/Or Date

dim UseDate As String
'Decide Either Cell Or Formatted Date
UseDate = Decision
 Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Sketch Updated: " & UseDate