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

Irazor
Irazor used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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

Selection.Insert Shift:=xlDown

That needs to be taken out.
Jeroen RosinkSoftware testing consultant

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

Ensure you’re charging the right price for your IT

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

Jeroen RosinkSoftware testing consultant

Commented:
Sorry forgot to remove the Selection.Insert line also.

Author

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?
Software testing consultant
Commented:
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

Author

Commented:
Thx for taking the time to get me this solution.
Jeroen RosinkSoftware testing consultant

Commented:
Glad to help,
thanks for the grade!
Jeroen

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial