Having trouble using designer and inputng VBA Code

Question

Can someone help me get my userform to submit into this table from cal worksheet?

Private Sub cmdbutton_submitform_Click()
    Dim emptyRow As Long

    'Make Sheet2 active
    Sheet2.Activate
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    'Transfer information
    Cells(emptyRow, 1).Value = txtbox_number.Value
    Cells(emptyRow, 2).Value = txtbox_rank.Value
    Cells(emptyRow, 3).Value = txtbox_Name.Value
    Cells(emptyRow, 4).Value = txtbox_height.Value
    Cells(emptyRow, 5).Value = txtbox_weight.Value
    Cells(emptyRow, 6).Value = txtbox_right_rm.Value
    Cells(emptyRow, 7).Value = txtbox_left_rm.Value
End Sub

Private Sub cmdbutton_submitform_Click() Dim emptyRow As Long 'Make Sheet2 active Sheet2.Activate 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'Transfer information Cells(emptyRow, 1).Value = txtbox_number.Value Cells(emptyRow, 2).Value = txtbox_rank.Value Cells(emptyRow, 3).Value = txtbox_Name.Value Cells(emptyRow, 4).Value = txtbox_height.Value Cells(emptyRow, 5).Value = txtbox_weight.Value Cells(emptyRow, 6).Value = txtbox_right_rm.Value Cells(emptyRow, 7).Value = txtbox_left_rm.Value End Sub — Johnathon Gray, Nov 22 '15 at 17:18

score 1 · Answer 1 · edited May 23 '17 at 12:23

I think you are getting confused with sheet codenames and sheet names (see this). Try

Private Sub cmdbutton_submitform_Click()
    Dim emptyRow As Long

    With Worksheets("Sheet2")
        'Determine emptyRow
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        'Transfer information
        .Cells(emptyRow, 1).Value = txtbox_number.Value
        .Cells(emptyRow, 2).Value = txtbox_rank.Value
        .Cells(emptyRow, 3).Value = txtbox_Name.Value
        .Cells(emptyRow, 4).Value = txtbox_height.Value
        .Cells(emptyRow, 5).Value = txtbox_weight.Value
        .Cells(emptyRow, 6).Value = txtbox_right_rm.Value
        .Cells(emptyRow, 7).Value = txtbox_left_rm.Value
    End With
End Sub

Awesome insight thank you... im learning everyday. Never touched vba until today — Johnathon Gray, Nov 23 '15 at 20:15

score 0 · Answer 2 · answered Nov 22 '15 at 18:13

Using Worksheet.Activate method likely loses the parent form reference that is required to correctly get the text box data from the user form. Within this Private Sub you should be able to reference Sheet2 by its Worksheet .CodeName property and use Me to reference the user form as the parent of the text boxes.

Private Sub cmdbutton_submitform_Click()
    Dim emptyRow As Long

    'Reference Sheet2 by CodeName as the parent worksheet of the .Cells
    With Sheet2
        'Determine emptyRow
        emptyRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        'Transfer information
        .Cells(emptyRow, 1).Value = Me.txtbox_number.Value
        .Cells(emptyRow, 2).Value = Me.txtbox_rank.Value
        .Cells(emptyRow, 3).Value = Me.txtbox_Name.Value
        .Cells(emptyRow, 4).Value = Me.txtbox_height.Value
        .Cells(emptyRow, 5).Value = Me.txtbox_weight.Value
        .Cells(emptyRow, 6).Value = Me.txtbox_right_rm.Value
        .Cells(emptyRow, 7).Value = Me.txtbox_left_rm.Value
    End With
End Sub

I found it a little odd that you were identifying the worksheet with a Worksheet .CodeName property rather than a Worksheet .Name property. I've included a couple of links to make sure you are using the naming conventions correctly. In any event, I've use a With ... End With statement to avoid repeatedly reidentifying the parent worksheet.

Having trouble using designer and inputng VBA Code

2 Answers2