'VB Excel 2002 using Visual Basic to add new rows with a user form and then calculate cell values'

Hi,
I've been working my way through a book and have got to a user form section. I realize that I might be able to use something like it at work.

I've created a user form that adds a new row at the bottom of the sheet. In column D I would like to add the numeric values together and give the answer in the very bottom cell of that column.
Also to have a English pound sign.
Not all the columns input will be numeric it might have the letters TBI.

I'll add the spreadsheet t ee in a moment. Also I'll add the code snippet which is pretty much out of the book I've just added another column.

Private Sub btn_EmpOK_Click()
Dim LastRow As Long
Dim FinalRow As Long
Dim i As Integer
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Selection.Insert Shift:=xlDown
Cells(LastRow, 1).Value = tb_EmpName.Value
Cells(LastRow, 2).Value = tb_EmpPosition.Value
Cells(LastRow, 3).Value = tb_EmpHireDate.Value
Cells(LastRow, 4).Value = tb_count.Value
 
End Sub

Open in new window

IrazorAsked:
Who is Participating?
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.

IrazorAuthor Commented:
IrazorAuthor Commented:
Oops, I didn't mean to leave in the line

Selection.Insert Shift:=xlDown

That needs to be taken out.
Jeroen RosinkSoftware testing consultantCommented:
perhaps this macro helps you out.
Made sure that the date is formated correctly and the currency value

I hope this works for you
regards,
Jeroen
Private Sub btn_EmpOK_Click()
Dim LastRow As Long
Dim FinalRow As Long
Dim i As Integer
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Selection.Insert Shift:=xlDown
Cells(LastRow, 1).Value = tb_EmpName.Value
Cells(LastRow, 2).Value = tb_EmpPosition.Value
Cells(LastRow, 3).Value = Format(tb_EmpHireDate.Value, "MM/DD/YYYY")
Cells(LastRow, 4).Value = Format(tb_count.Value, "[$£-809]#,##0.00")
 
End Sub

Open in new window

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!

Jeroen RosinkSoftware testing consultantCommented:
Sorry forgot to remove the Selection.Insert line also.
IrazorAuthor Commented:
Hi roos01,
Thx for taking an interest. Actually this doesn't work on the spreadsheet I provided. I'm talking about the formatting that you've added. Also don't forget that I wanted it give the value of column D.

Quote:
I would like to add the numeric values together and give the answer in the very bottom cell of that column.

Did you try it in the spreadsheet I provided? And did it work for you?
Jeroen RosinkSoftware testing consultantCommented:
Perhaps this one works out better




Private Sub btn_EmpOK_Click()
Dim LastRow As Long
Dim FinalRow As Long
Dim i As Integer
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Cells(LastRow, 1).Value = tb_EmpName.Value
Cells(LastRow, 2).Value = tb_EmpPosition.Value
Cells(LastRow, 3).NumberFormat = "MM/DD/YYYY"
Cells(LastRow, 3).Value = tb_EmpHireDate.Value
Cells(LastRow, 4).NumberFormat = "[$£-809]#,##0.00"
Cells(LastRow, 4).Value = tb_count.Value
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
Cells(LastRow + 1, 4) = WorksheetFunction.Sum(Range(Cells(1, 4), Cells(LastRow, 4)))
Cells(LastRow + 1, 4).NumberFormat = "[$£-809]#,##0.00"
End Sub

Open in new window

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
IrazorAuthor Commented:
Thx for taking the time to get me this solution.
Jeroen RosinkSoftware testing consultantCommented:
Glad to help,
thanks for the grade!
Jeroen
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
Visual Basic Classic

From novice to tech pro — start learning today.