Storing Data from User Form to Excel Sheet

[shreyas17]

Hi Everyone,

I have created a user form and have a submit button such that whenever the submit button is clicked the data get automatically stored column wise in another worksheet of the same workbook. I have written the below code. However, each time I click on the submit button the data gets replaced by the new one , I want it to keep adding data into the next rows.

Private Sub CommandButton10_Click()

Dim msgValue As VbMsgBoxResult

msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")

If msgValue = vbNo Then Exit Sub

Dim wkdatabse As Worksheet
Dim Adddatabse As Range

Set wkdatabse = Sheet4

Set Adddatabse = wkdatabse.Range("A65365").End(xlUp).Offset(1, 0)


Adddatabse.Offset(0, 1).Value = TextBox10.Text
Adddatabse.Offset(0, 2).Value = TextBox3.Text
Adddatabse.Offset(0, 3).Value = TextBox7.Text

how to I change this to add value continuously to the rows without getting overwrite

 

[dmt32]

Hi,
see if this update to your code helps

Private Sub CommandButton10_Click()
    
    Dim wkdatabse   As Worksheet
    Dim AddDatabse  As Range
    Dim msgValue    As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want To save the data?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
    Set wkdatabse = Sheet4
    
    Set AddDatabse = wkdatabse.Cells(wkdatabse.UsedRange.Rows.Count + 1, 1)
    
    With AddDatabse
        .Offset(0, 1).Value = TextBox10.Text
        .Offset(0, 2).Value = TextBox3.Text
        .Offset(0, 3).Value = TextBox7.Text
    End With
End Sub

Dave

 

[shreyas17]

Thank You for your help .. works perfectly