Use a checkbox to copy a row and paste at end

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Jul 17, 2018 at 07:00 AM
 Blocked Profile - Jul 17, 2018 at 07:05 PM

Relatively new to VBA so please bear with me.

I am looking to add a checkbox button that when clicked will replicate the row of data at the end of the excel worksheet and activate a form to add additional info to the current row on the excel. Basically this is to mark a task as complete, then reopen the task at the bottom with new dates, so that it can be undertaken in a few weeks time.

I have produced a form for the initial data entry as follows...


Private Sub CommandButton1_Click()

Dim LastRow As Range
Dim TaskManagerTable As ListObject

'Add row to bottom of task manager table

'Enter data from form into new row
Set TaskManagerTable = ActiveSheet.ListObjects("Tasks")
Set LastRow = TaskManagerTable.ListRows(TaskManagerTable.ListRows.Count).Range

With LastRow

'Get the highest number in column A and assign it to variable MaxNumber
MaxNumber = Application.WorksheetFunction.Max(Range("A:A"))

.Cells(1, 1) = MaxNumber + 1
.Cells(1, 2) = Now
.Cells(1, 4) = TaskType.Value
.Cells(1, 3) = IssuerName.Value
.Cells(1, 5) = DeptName.Value
.Cells(1, 7) = RoutineNo.Value
.Cells(1, 6) = MachineNo.Value
.Cells(1, 9) = Description.Value

Unload TaskManagerForm

End With
End Sub


The new form captures, TimeTaken, NoEmployees and DoneBy.

Hope someone can help.

1 response

Blocked Profile
Jul 17, 2018 at 04:27 PM
YOu have almost successfully created a database! If you are new to VBA, then you would pick up access just as fast, and would get a better product for what you have described! EXCEl is for calculations and such, not data entry and record keeping.

Build some tables (if you need help, just ask!)
Build a form that is mapped to the table (the form will automatically input the data into a blank "ROW"! Magic!
Build reports based on what you need back out!

It is that simple, and easier than learning how to code, as there is no code besdies SQL! Which BTW is an actual code, and pretty robust on a massive machine like an IBM with BI!
PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Jul 17, 2018 at 05:58 PM
Thanks, I'll do are right that if I am going to put the effort into learning as I go, I may as well learn the best tool for the job. I just went with Excel because it was my comfort area (less VBA) and because it is what is currently being used (albeit at a very basic level).

I'm sure I will need some guidance on this, but I will try to figure my way through solo, to start with at least.
Blocked Profile
Jul 17, 2018 at 07:05 PM
I will be here. Most use excel in this manner, because they can visual it! You will be able to do the same thing with a DB! I will be here!