Use a checkbox to copy a row and paste at end

Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
- Jul 17, 2018 at 07:00 AM - Latest reply:
Posts
10873
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
- Jul 17, 2018 at 07:05 PM
Hi,

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...

CODE

Private Sub CommandButton1_Click()

Dim LastRow As Range
Dim TaskManagerTable As ListObject

'Add row to bottom of task manager table
ActiveSheet.ListObjects("Tasks").ListRows.Add

'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

END CODE

The new form captures, TimeTaken, NoEmployees and DoneBy.

Hope someone can help.
See more 

Your reply

3 replies

Best answer
Posts
10873
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
Jul 17, 2018 at 04:27 PM
1
Thank you
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!

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1702 users this month

Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
- Jul 17, 2018 at 05:58 PM
Thanks, I'll do that...you 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.
Posts
10873
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
- 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!
Respond to ac3mark