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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
NorieAnalyst Assistant Commented:
How are you doing the formatting, it appears to be 'banding'?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jonh SmithAuthor Commented:
I didnt wrote code for the format, I just fill the values into an allready formated(by hand) cell range!
0
NorieAnalyst Assistant Commented:
Are you using a table?
0
Jonh SmithAuthor Commented:
No. Just normal cells!
0
NorieAnalyst Assistant Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.