Push down values and add in the new row!

Hi,

Self-learning VBA and im in UserForms for now, I have a dinner planner set up as a user form I would like to know how add the new values in Row 2 instead of adding the new value to Row 5, so that the latest plans allways stay on top and they push down the oldest ones.

I try this
Range("A2:G2").Insert Shift:=xlDown

Open in new window

but it pushes the whole row down adding the new values in the end and copying and pasting the fill format to the row where i want the values to be!

dinner.png
Heres is the code when i press "OK" after filling the "Add New Person" form.

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "Yes"
Else
    Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

Range("A2:G2").Insert Shift:=xlDown

End Sub

Open in new window


Thanks in advance for any help!

 J.Smith
Jonh SmithAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Change emptyRow to 2 and move the code to insert the empty row to the start.
Private Sub OKButton_Click()
Dim emptyRow As Long

    emptyRow = 2
    
    With Sheet1

        ' insert row at top
        .Range("A2:G2").Insert Shift:=xlDown

        ' Transfer information
        .Cells(emptyRow, 1).Value = NameTextBox.Value
        .Cells(emptyRow, 2).Value = PhoneTextBox.Value
        .Cells(emptyRow, 3).Value = CityListBox.Value
        .Cells(emptyRow, 4).Value = DinnerComboBox.Value

        If DateCheckBox1.Value = True Then .Cells(emptyRow, 5).Value = DateCheckBox1.Caption

        If DateCheckBox2.Value = True Then .Cells(emptyRow, 5).Value = .Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

        If DateCheckBox3.Value = True Then .Cells(emptyRow, 5).Value = .Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

        If CarOptionButton1.Value = True Then
            .Cells(emptyRow, 6).Value = "Yes"
        Else
            .Cells(emptyRow, 6).Value = "No"
        End If

        .Cells(emptyRow, 7).Value = MoneyTextBox.Value
        
    End With

End Sub

Open in new window

1
 
Jonh SmithAuthor Commented:
Great it works! But it is still pulling the Fill Color on "A1:G1" down. Any way to keep the format on A2:G2 intact? Its supossed to be the same as A3:G3!
0
 
NorieVBA ExpertCommented:
How are you doing the formatting, it appears to be 'banding'?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jonh SmithAuthor Commented:
I didnt wrote code for the format, I just fill the values into an allready formated(by hand) cell range!
0
 
NorieVBA ExpertCommented:
Are you using a table?
0
 
Jonh SmithAuthor Commented:
No. Just normal cells!
0
 
NorieConnect With a Mentor VBA ExpertCommented:
It might actually be worth converting to a table so you can preserve formatting throughout, including the rows being pushed down - note that the last row of your data has no formatting.

To convert to a table select the existing data, goto Format as Table, pick a suitable table style and when prompted check the My table has headers box.

Once you have the table you can replace the insert in the code with this.
    Sheet1.ListObjects(1).ListRows.Add (1)

Open in new window

That adds a blank row at the top and preserves the formatting through out.
1
 
Jonh SmithAuthor Commented:
Clean and easy explanation!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.