VBA to add new row at top of table - keep number formatting & size

[pg1955]

Hello! I have successfully used a command button to add in a row to the first position in a table, but am looking to also keep the first column formatted as a date. The purpose would be to keep the most recent entry at the top always, to keep wrap text on and to keep the row auto-sizing to the length of text.

This is the current code which is just to add the row for the note:

Private Sub CommandButton1_Click()

ThisWorkbook.Sheets("Notes").Visible = True
ThisWorkbook.Sheets("Notes").Select

ThisWorkbook.Sheets("Notes").Range("A10").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Notes").Range("A10:B10").Select
Selection.Borders.Weight = xlThin

End Sub

 

[RobP]

Hi ,

you could maybe add this code to the bottom of your command button if I understood well ?

Selection.NumberFormat = "dd/mm/yyyy"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Columns.AutoFit

 

[pg1955]

YES Thank you! I decided instead of the number format to just use "today" because I want all selections time stamped in column A. This is what I ended up with
Private Sub CommandButton1_Click()

ThisWorkbook.Sheets("Notes").Visible = True
ThisWorkbook.Sheets("Notes").Select

ThisWorkbook.Sheets("Notes").Range("A10").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Notes").Range("A10:B10").Select
Selection.Borders.Weight = xlThin

ThisWorkbook.Sheets("Notes").Range("A10").Select
Selection.Formula = "=today()"

With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Columns.AutoFit

End Sub

 

[RobP]

great, glad to be of help.
Rob