VBA glitch on User Form

[kyjae]

Hi,

Firstly I'm a complete VBA newbie so bear with me!

I'm exploring and learning about user forms and I've cracked the basics. However, the problem is that whenever I input data via the user form, it replaces what was input before.

So my VBA for a command button to capture the data is this.....

Private Sub cmdSave_Click()
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = DatePick
Cells(erow, 2) = CheckBox1.Value
Cells(erow, 3) = cmbActivity.Text
Cells(erow, 4) = tbDuration.Text
Cells(erow, 5) = tbMileage
Cells(erow, 6) = tbLogDetails.Text
End Sub

When I run the form for the first time it places the captured data on row 2 in Sheet1 as it should under the correct headings in row 1. However, if I then submit some new data via the user form, the data in row 2 is replaced with the newly submited data. In fact every time the user form is run, it just replaces what's on row 2 of Sheet1 with new data. Where am I going wrong?

Many thanks.

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Andy Pope]

try this modification

Please Login or Register  to view this content.

[kyjae]

Example file attached. Many thanks.

[kyjae]

Thanks Andy but this returns an error

[Andy Pope]

I think you are confusing sheet tab text with sheet code name.

Please Login or Register  to view this content.

sheet tab name 'Sheet1' is in fact code name Sheet5

so basically the end row as being determined from the row sheet and output was to the activesheet when I think you wanted adding to the log on 'Sheet1'

[Norie]

Sheet1 is actually the codename for the 'SUMMARY', so that's what your code is referring to when finding the next empty row.

If you want the data to goto the sheet with the tab name 'Sheet1' and codename Sheet5 try this.

Please Login or Register  to view this content.

[kyjae]

Works perfectly!

Thank you so much, Norie.

[kyjae]

Norie,

Just a quick aside - the line

.Cells(erow, 4) = tbDuration.Text

what do I need to change '.text' to to ensure the data is captured as a number?

Many thanks

[Norie]

It's coming out as a number, specifically a time, on the worksheet when I try it.

Mind you the value from tbMileage is flagged as 'Number stored as Text' when transferred to the sheet.

If you want to ensure both go to the sheet properly as numeric data try this.

Please Login or Register  to view this content.

PS Can you add code tags when posting code?

[kyjae]

Thank you Norie