VBA to load data from userform to next available row

[fireslguk]

problem - require vba when commandbutton click in userform1

goto sheet "log"

for spare row

textbox1.text to be pasted to column A
textbox2.text to be pasted to column B
textbox3.text to be copied to column c

next time userform loaded and command button clicked

next spare row same as above

and so on

 

[Logit]

.
Here is a simple database project for you to review. It will perform the function you desire with
some editing for your specific project.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/73U9ameaosTXCjqF4Wrb8uVyfMccQuIFSiIwIiL7X6Q

 

[fireslguk]

Thanks but how do I adapt as I want the data to go in Sheet2(LOG) ?

 

[My Aswer Is This]

Try this:
Assuming your sheet is named log

Private Sub CommandButton1_Click()
'Modified  11/26/2018  3:10:30 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("log").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("log")
    .Cells(Lastrow, 1).Value = TextBox1.Value
    .Cells(Lastrow, 2).Value = TextBox2.Value
    .Cells(Lastrow, 3).Value = TextBox3.Value
End With
Application.ScreenUpdating = True
End Sub

 

[fireslguk]

That’s great thankyou - problem though my log sheet is password protected so unless unprotected this vba stops

 

[My Aswer Is This]

That’s great thankyou - problem though my log sheet is password protected so unless unprotected this vba stops

You never mentioned the sheet being protected.
You need to unprotect the sheet and then run the script.

 

[fireslguk]

You never mentioned the sheet being protected.
You need to unprotect the sheet and then run the script.

My apologies sorry

Rather than manual unprotect the sheet to run code can the vba be adjusted to do this itself and reprotect ?

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[rasinc]

fireslguk

I don't know exactly how you have your sheet protected, so I am going to suggest that because you can manually unprotect it, start recording a macro before you start the process and reprotect when you are done, then stop recording. The code will be save to the macro for you.

 

[My Aswer Is This]

Yes I agree. There are several ways to protect sheets so I had no way of knowing either. And what needs protecting just one particular sheet. The recorder would be best and then put that code before my script runs.

fireslguk

I don't know exactly how you have your sheet protected, so I am going to suggest that because you can manually unprotect it, start recording a macro before you start the process and reprotect when you are done, then stop recording. The code will be save to the macro for you.