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

 

[Norie]

Try this.

Option Explicit
 
Private Sub cmdTransfer_Click()
Dim NextRow As Long
Dim rng As Range

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

    rng.Offset(0, 0).Value = Me.txtCase.Value
    rng.Offset(0, 1).Value = Me.txtEnd.Value
    rng.Offset(0, 2).Value = Me.txtReview.Value
    rng.Offset(0, 3).Value = Me.cboDx.Value
    rng.Offset(0, 4).Value = Me.txtAge.Value
    rng.Offset(0, 5).Value = Me.cboAge.Value
    rng.Offset(0, 6).Value = Me.cboGender.Value
    rng.Offset(0, 7).Value = Me.cboStatus.Value
    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.Value
    rng.Offset(0, 13).Value = Me.txtType.Value
    rng.Offset(0, 14).Value = Me.txtKey.Value
 
End Sub

 

[cmccabe]

I tried the code and nothing happens when I click the button to add the entry to the spreadsheet from the userform. Thanks.

 

[Norie]

Can you try stepping through the code?

Put a breakpoint (F9) on the Sub heading (Private Sub....).

Then open the userform, put some values in the textboxes.

Press the Transfer button and when the code stops executing press F8 to step through it.

That should give you some idea what's happening.

If you do that post back.

 

[cmccabe]

I put a breakpoint on the Private Sub and then exexute the transfer. if I step through the Private Sub gets highlighted in yellow, I use F8 and the NextRow = Worksheets("HSCBM_RD").Range("A" & Rows.Count).End(xlUp).Row + 1 is highlighted in yellow anlong with what is below it. Thanks.

Option Explicit
 
Private Sub cmdTransfer_Click()
Dim NextRow As Long
Dim rng As Range
    
    NextRow = Worksheets("HSCBM_RD").Range("A" & Rows.Count).End(xlUp).Row + 1
 
    Set rng = Worksheets("HSCBM_RD").Range("A" & NextRow)
    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
 
End Sub

 

[Norie]

So it's working now?

 

[cmccabe]

It doesn't seem to. Without the breakpoint after the Private Sub when I click the button nothing happens. With the breakpoint after the Private Sub when I click the button the code is displayed as I attached. Im not sure what this means. Thanks.

 

[Norie]

Did you step through using F8?

If you did each line of code should be highlighted.

You can also check the value of NextRow by simply hovering over it when you are doing this.

I've set things up as closely as I can to what you have and it works fine.

The only possible difference I can think of at the moment is that you have more than one workbook open and they both have a worksheet with the name 'HSCBM_RD.

If that was the case there's a possibility that the data could be going to the wrong workbook.

That's unlikely though but you never know.

 

[cmccabe]

I did step though the code and each line of code was highlighted and the value did show up when I hoovered over it, bit it did not transfer to the spreadsheet. I also checked there is only one sheet with the 'HSCBM_RD name. Thanks for the help.

 

[Norie]

This definitely works for me.

Did you check there were no other workbooks open?

Is there any other code?

Have you searched the worksheet for the values that should be inserted?

What value is NextRow when the code is run?