Using Active X Controls to copy and paste between Worksheets

[craigs23]

Hi All,

I am currently working on a project, where I woudl like to use a worksheet as a data entry tool instead of a userfrom.

I am looking to use active X controls and will fromat the input sheet to resemble a userform.

So far I have managed to tweak some lines of code that I found that once attached to a command button will take the data from indivdual cells and paste it into the next empty row on a different worksheet.

I would also like to add a couple of text boxes into the data entry so that the op can add more detailed information.

However what I can't seem to grasp is how to add to the code to allow me to copy the individual cell data and also copy the Textbox contents and then past them into the new sheet on the same line.

I have attached the code I already have (that copies and pastes cell data) below:

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long, cls
cls = Array("A2", "B3", "C2", "D3", "E2", "F3")
With Sheets("Sheet2")
CommandButton2_Click
    LR = WorksheetFunction.Max(1, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        Me.Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)
    Next i
End With
End Sub

Can anyone Help?

Thanks in advance