• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying data from userform to Excel sheet

Hi Ninjas,

My userform is now working great, thanks to Hui :DD .. but now what I am trying to do is transfer the data from the userform to a certain sheet. It moves the data to the Tracker sheet but it only pastes data in Row 2, my headers start at row 3 .. and when I try to save another entry , it overrides the previous data.

Appreciate the help!
Thanks in advance! :DD

Private Sub CommandButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Tracker")
'find first row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("B" & irow) = ComboBox1.Value
.Range("E" & irow) = TextBox2.Value
.Range("F" & irow) = TextBox1.Value
.Range("G" & irow) = TextBox3.Value
.Range("H" & irow) = TextBox4.Value
.Range("I" & irow) = TextBox5.Value
.Range("J" & irow) = TextBox6.Value
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
End Sub
 
Hi,

Your first columns might be blank.

Change bold part to non blank column or where you wish to paste the data.

irow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
 
By the way .. there will be multiple users for this file .. is it possible to have the username included in the timestamp? I used this code for the date & time stamp ..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Cells(Target.Row, 3).Value = Now
Cells(Target.Row, 4).Value = Now
End If
End Sub
 
Application.username is how to access the Username

Do you mean like:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
  Cells(Target.Row, 3).Text = Application.UserName
  Cells(Target.Row, 4).Value =  Now
End If
End Sub
 
Application.username is how to access the Username

Do you mean like:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
  Cells(Target.Row, 3).Text = Application.UserName
  Cells(Target.Row, 4).Value =  Now
End If
End Sub

I'm getting an error on this line .. not sure why ..

Cells(Target.Row, 3).Text = Application.UserName
 
Back
Top