User Form Macro Question

[corcelle]

Hello,
I have been slowly learning VBA language and macros, and built my first user form yesterday. I was able to successfully get the form to open via a button, clear entry, close the form, and produce an error message when the form isn't completely filled out. However, the final and most important step involves add the information entered into the user form into the actual excel workbook, and that part is not using.


I have been using the following with statement, and for some reason it is posting the first entry into my desired field but then skipping a bunch of columns before posting the rest. Any suggestions?


RowCount = Worksheets("Current Projects").Range("D2").CurrentRegion.Rows.Count
With Worksheets("Current Projects").Range("D2")
.Offset(RowCount, 0).Value = Me.txtName.Value
.Offset(RowCount, 1).Value = Me.txtDes.Value
.Offset(RowCount, 2).Value = Me.txtType.Value
.Offset(RowCount, 3).Value = Me.txtReq.Value
.Offset(RowCount, 4).Value = Me.txtLOB.Value
.Offset(RowCount, 5).Value = Me.txtRDate.Value
.Offset(RowCount, 6).Value = Me.TxtIE.Value
.Offset(RowCount, 7).Value = Me.txtAgn.Value
.Offset(RowCount, 8).Value = Me.txtDDate.Value
End With


Thank you for your help!
Corcelle

 

[Chris84]

Hi

Congratulations on creating your first UserForm, there great fun!

try this code

Option Explicit
Private Sub Submit_Click()
Dim ws As Worksheet
Dim lRow As Long
Set ws = Worksheets("Current Projects")
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
    .Cells(lRow, 1).Value = Me.txtName.Value
    .Cells(lRow, 2).Value = Me.txtDes.Value
    .Cells(lRow, 3).Value = Me.txtType.Value
    .Cells(lRow, 4).Value = Me.txtReq.Value
    .Cells(lRow, 5).Value = Me.txtLOB.Value
    .Cells(lRow, 6).Value = Me.txtRDate.Value
    .Cells(lRow, 7).Value = Me.txtIE.Value
    .Cells(lRow, 8).Value = Me.txtAgn.Value
    .Cells(lRow, 9).Value = Me.txtDDate.Value
End With
End Sub

This will find the next empty row in Current Projects and transfer the data from your userform.

Please post back if you have any issues

Thanks