userform

[cmccabe]

I am trying to use a userform to enter data and can not seem to have the next data entry appear on a new line. It is a simple userform that has about 10 fields that are inputted and then the user clicks a button that transfers the data and allows for another entry. However, i do not seem to be doing that correctly. Thanks.

Private Sub cmdTransfer_Click()
Dim RowCount As Long
'Range("A2") = txtCase.Value
'Range("B2") = txtEnd.Value
'Range("C2") = txtReview.Value
'Range("D2") = cboDx.Text
'Range("E2") = txtAge.Value
'Range("F2") = cboAge.Text
'Range("G2") = cboGender.Text
'Range("H2") = cboStatus.Text
'Range("I2") = cbLSAB.Text
'Range("J2") = cbSSPHR.Text
'Range("K2") = cbSSPLR.Text
'Range("L2") = cbSBT.Text
'Range("M2") = cboDonor.Text
'Range("N2") = txtType.Text
'Range("O2") = txtKey.Text
RowCount = Worksheets("HSCBM_RD").Range("A2").CurrentRegion.Rows.Count
With Worksheets("HSCBM_RD").Range("A2")
    .Offset(RowCount, 0) = Me.txtCase.Value
    .Offset(RowCount, 1) = Me.txtEnd.Value
    .Offset(RowCount, 2) = Me.txtReview.Value
    .Offset(RowCount, 3) = Me.cboDx.Value
    .Offset(RowCount, 4) = Me.txtAge.Value
    .Offset(RowCount, 5) = Me.cboAge.Value
    .Offset(RowCount, 6) = Me.cboGender.Value
    .Offset(RowCount, 7) = Me.cboStatus.Value
    .Offset(RowCount, 8) = Me.cbLSAB.Value
    .Offset(RowCount, 9) = Me.cbSSPHR.Value
    .Offset(RowCount, 10) = Me.cbSSPLR.Value
    .Offset(RowCount, 11) = Me.cbSBT.Value
    .Offset(RowCount, 12) = Me.cboDonor.Value
    .Offset(RowCount, 13) = Me.txtType.Value
    .Offset(RowCount, 14) = Me.txtKey.Value
End With
End Sub

 

[cmccabe]

There are no other workbooks open and there is no other code is above that. When I hoover over both:

 NextRow = WorksheetsHSCBM_RD").Range("A" & Rows.Count).End(xlUp).Row + 1
 
    Set rng = Worksheets("HSCBM_RD").Range("A" & NextRow)
Nothing appears and those two lines of code do not get highlighted.
 
rng.Offset(0, 0).Value = Me.txtCase.Text
    rng.Offset(0, 1).Value = Me.txtEnd.Text
    rng.Offset(0, 2).Value = Me.txtReview.Text
    rng.Offset(0, 3).Value = Me.cboDx.Text
    rng.Offset(0, 4).Value = Me.txtAge.Text
    rng.Offset(0, 5).Value = Me.cboAge.Text
    rng.Offset(0, 6).Value = Me.cboGender.Text
    rng.Offset(0, 7).Value = Me.cboStatus.Text
    rng.Offset(0, 8).Value = Me.cbLSAB.Value
    rng.Offset(0, 9).Value = Me.cbSSPHR.Value
    rng.Offset(0, 10).Value = Me.cbSSPLR.Value
    rng.Offset(0, 11).Value = Me.cbSBT.Value
    rng.Offset(0, 12).Value = Me.cboDonor.Text
    rng.Offset(0, 13).Value = Me.txtType.Text
    rng.Offset(0, 14).Value = Me.txtKey.Text

These are highlighted and the values entered in the text boxes appear when I hoover over.
Thanks.

 

[Norie]

What is the value of NextRow?

 

[cmccabe]

In the code?

NextRow = Worksheets("HSCBM_RD").Range("A" & Rows.Count).End(xlUp).Row + 1


When I step through and hoover over NextRowit no value appears. I'm not sure if that is the information you desire. The data entry doesn't tranfer from the userform to the spreadsheet either. Thanks.

 

[cmccabe]

I found the data but it is in row 204. It should appear in row 3 as that is the first blank row. Thanks.

 

[Norie]

That means the data is being transferred to the worksheet but not where you expect it to be.

Is there anything, including formatting, in column A?

 

[cmccabe]

Column A is formatted to a number... it is entered in a textbox but since it is a tracking identifier a number is used. Thanks.

 

[Norie]

Can you upload the file somewhere?

I can't see why the code doesn't work.

The reason I asked about the formatting is because sometimes if you try and find the last row in a column with formatting it doesn't work.

If you can upload the file I can take a look at it.

 

[cmccabe]

I am not sure where to upload it, can I email it? Thanks.

 

[Norie]

I'll PM you.

 

[cmccabe]

There were formatted cells in 199-203, that is where the groups for the pull-downs start. So it was putting the data in 204 (after the formatting). Is there a way that the previous data entry on the userform can be cleared so that multiple entries can be made? So, iwhen the entry for 1 is transfered it is also cleared so a new entry can be made? Thanks for all you help and sorry for the confusion and if I was a bother.