We help IT Professionals succeed at work.

VBA code for Simple Excel worksheet

Kevin
Kevin asked
on
Good Morning,

I found the attached spreadsheet online. I added a button and found the code to insert a new row right beneath the titles.

This is working great, but only partially, as when I click the button it doesn’t keep the formatting of each of the cells.

I would like the new row to be inserted on row 5 and keeping the format of the cells I have below.

Can someone fix this please?

I would like where when I click the button for a new item the below formats are included in the new row:

*Project / Task = Font Arial 10, Middle Align and Align Left, Wrap text, Bold Font Disabled
*Status = Font Arial 10, Middle Align and Center, Wrap text, Drop List with Status column on Lists worksheet, Bold Font Disabled
*Priority = Font Arial 10, Middle Align and Center, Drop List with Priority column on Lists worksheet, Bold Font Disabled
*Category = Font Arial 10, Middle Align and Center, Wrap text, Drop List with Category column on Lists worksheet, Bold Font Disabled
*Due Date = Font Arial 10, Middle Align and Center, date in format MM/DD/YYYY, Bold Font Disabled
*Affected Users = Font Arial 10, Middle Align and Center, Drop List with Affected Users column on Lists worksheet, Bold Font Disabled
*Reported Date = Font Arial 10, Middle Align and Center, date in format MM/DD/YYYY, Bold Font Disabled
*Completion Date = Font Arial 10, Middle Align and Center, date in format MM/DD/YYYY, Bold Font Disabled
*Owner = Font Arial 10, Middle Align and Center, Drop List with Owner column on Lists worksheet, Bold Font Disabled
*Ticket ID = Font Arial 10, Middle Align and Align Left, Bold Font Disabled
*Notes = Font Arial 10, Middle Align and Align Left, Wrap text, Bold Font Disabled

Kind regards,
K

PS. Please do not recommend other platforms or services free or for purchase, that could be used as an alternative to my workbook. I am happy with the Excel Workbook and would just like it getting working to the way I have stated above.
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You forgot to upload your workbook.
KevinInformation Technology

Author

Commented:
Woops... sorry. See attached.
ToDo.xlsm
Information Technology
Commented:
Nevermind.

I figured it out myself using the below code:

Private Sub CommandButton1_Click()

Dim mySheets
    Dim i As Long

mySheets = Array("ToDoList")

For i = LBound(mySheets) To UBound(mySheets)
    With Sheets(mySheets(i))
        .Range("A5").EntireRow.Insert Shift:=xlDown
        .Range("A5:K5").Font.Name = "Arial"
        .Range("A5:K5").Font.Size = "10"
        .Range("A5:K5").Font.Bold = False
        .Range("E5,G5,H5").NumberFormat = "mm/dd/yyyy"
        .Range("A5,B5,J5,K5").WrapText = True
        .Range("B5:J5").VerticalAlignment = xlCenter
        .Range("A5,K5").HorizontalAlignment = xlLeft
        .Range("A5").Select
        
    End With
Next i

End Sub

Open in new window