VBA code for automating a sheet

Question

1) I have created a form in VBA and I have given connection to first sheet and when I click on the button the form is getting pop up to enter data, but what I want is, I want to assign a button in first sheet and when i click on the button the form should appear and when i insert data into the form, it should appear in the second sheet.

2) I am inserting data into sheet for only four rows and after completion of that if want to modify particular column or particular row of data how can we do that, i need someone to suggest me on this and i also request you to send me the code how to modify.

3) And I also request you to send me the code to clear the sheet if i want to enter new data.

I would be glad if someone helps me on these 3 points.

Private Sub cmdAdd_Click()
Dim i As Integer

'position cursor in the correct cell B9.
Range("B9:P40").Select
i = 1 'set as the first ID

'validate first four controls have been entered...
If Me.txtFName.Text = Empty Then 'Firstname
    MsgBox "Please enter firstname.", vbExclamation
    Me.txtFName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtSName.Text = Empty Then 'Surname
    MsgBox "Please enter surname.", vbExclamation
    Me.txtSName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtFuName.Text = Empty Then 'FullName
    MsgBox "Please enter fullname.", vbExclamation
    Me.txtFuName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtDName.Text = Empty Then 'Designation
    MsgBox "Please enter Designation.", vbExclamation
    Me.txtDName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

'if all the above are false (OK) then carry on.
'check to see the next available blank row start at cell B9...
Do Until ActiveCell.Value = Empty
    ActiveCell.Offset(1, 0).Select 'move down 1 row
    i = i + 1 'keep a count of the ID for later use
Loop

'Populate the new data values into the 'Data' worksheet.
ActiveCell.Value = i 'Next ID number
ActiveCell.Offset(0, 1).Value = Me.txtFName.Text 'set col B
ActiveCell.Offset(0, 2).Value = Me.txtSName.Text 'set col C
ActiveCell.Offset(0, 3).Value = Me.txtFuName.Text 'set col D
ActiveCell.Offset(0, 4).Value = Me.txtDName.Text 'set col E

'Clear down the values ready for the next record entry...
Me.txtFName.Text = Empty
Me.txtSName.Text = Empty
Me.txtFuName.Text = Empty
Me.txtDName.Text = Empty

Me.txtFName.SetFocus 'positions the cursor for next record entry

End Sub

Private Sub cmdClose_Click()
    'Close the form (itself)
    Unload Me
End Sub

Answer 1

A) Place a button in Sheet one.

If you are using a form control then place this code in a module and then right click on the button and click on assign macro and then link it to the below code

Sub Launch()
    UserForm1.Show
End Sub

If you are using an ActiveX Control, then double click it in Design Mode and use this code

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

Regarding showing the data in Sheet2, in say "Ok" button of the form, link it with the 2nd sheet. For example.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws           
        '~~> Assuming you want to write to only 1st 4 rows as you mentioned
        .Range("A1").Value = TextBox1.Text
        .Range("A2").Value = TextBox2.Text
        .Range("A3").Value = TextBox3.Text
        .Range("A4").Value = TextBox4.Text
    End With
End Sub

---

B) For this, I would recommend taking user's input and then based on that show the relevant form.

If user selects the first option then show your Data Entry Userform else show the Edit Userform which will look like Data Entry Userform but will have a UserForm_Initialize() which will pull the data from the sheet depending on whether user wants to edit the row or a column. How you choose to take that input is what I leave it to you. For example

Private Sub UserForm_Initialize()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        TextBox1.Text = .Range("A1").Value
        TextBox2.Text = .Range("A2").Value
        TextBox3.Text = .Range("A3").Value
        TextBox4.Text = .Range("A4").Value
    End With
End Sub

---

C) To clear the sheet you can use this code.

Private Sub CommandButton2_Click()
    ThisWorkbook.Sheets("Sheet2").Cells.ClearContents
End Sub