Using VBA to add people to a table using form

[jermaindefoe]

Hey peeps, ive got a table on the bottom of my spreadsheet that has names of people in, first name in a clumn, last name in a coumn marks scored in a column and overall grade in a column. Ive created and coded a form which allows a user to enter the first name, last name and points but how do i get it to add it to the table, id really appreciate the help

thanks very much

ricki lambert

ive attatched my worksheet in hope somone can have a look, as for the form that allows user entry, i havent got sa button coded to open it yet so you have to push alt + f11 to open the vba code thingy :p

[royUK]

Debug your code and you will see that your code has too many End subs (should be exit Sub) in this Procedure - Private Sub enterbtn_Click()

Try this

Private Sub enterbtn_Click()
    Dim R      As Long
    If Me.firstnametxtb.Value = "" Then
        MsgBox "Please enter a First Name.", vbExclamation, "Student Entry"
        Me.firstnametxtb.SetFocus
        Exit Sub
    End If

    If Me.lastnametxtb.Value = "" Then
        MsgBox "Please enter a Last Name.", vbExclamation, "Student Entry"
        Me.lastnametxtb.SetFocus
        Exit Sub
    End If

    If Me.pointstxtb.Value = "" Then
        MsgBox "Please enter points achieved.", vbExclamation, "Student Entry"
        Me.pointstxtb.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.pointstxtb.Value) Then
        MsgBox "The Points box must contain a number.", vbExclamation, "Student Entry"
        Me.pointstxtb.SetFocus
        Exit Sub
    End If
    With Sheet1
        R = .Cells(Rows.Count, 1).End(xlUp).Row + 1
'add from form
        .Cells(R, 1).Value = Me.firstnamelbl
        'etc
    End With

End Sub

[jermaindefoe]

  With Sheet1
        R = .Cells(Rows.Count, 1).End(xlUp).Row + 1
'add from form
        .Cells(R, 1).Value = Me.firstnamelbl
        'etc
    End With

ok so would the next bit after

.Cells(R, 1).Value = Me.firstnamelbl

be;

.Cells(R, 2).Value = Me.lastnametxtb
.Cells(R, 3).Value = Me.pointstxtb

and in ur code u have it as firstnamelbl, wouldnt it be firtnametxtb????

thanks

sorry for being dumb lol

[jermaindefoe]

ok, so thanks to the code you have posted i have managed to get it to work, i just have two things i need to sort out

q1) whenevr the detials get entered the are put correctly in to the columns, however, for the formual t owork correctly i nthe other columns the points section thats entered from the form needs to be in number format, and by right cicking the cell and changing to number doesnt work, i have to input the data i nthe form, then click the green thing that says convert this to number, then the rest fo the formulae in the othe column works

q2) how do i add a button to the worksheet that will open the user form up, i realsie the code for a normally programemd button would be something like

userform.show()

thnanks peeps

[royUK]

Change the Number Format with the code

Private Sub enterbtn_Click()
   Dim R      As Long
    If Me.firstnametxtb.Value = "" Then
        MsgBox "Please enter a First Name.", vbExclamation, "Student Entry"
        Me.firstnametxtb.SetFocus
        Exit Sub
    End If

    If Me.lastnametxtb.Value = "" Then
        MsgBox "Please enter a Last Name.", vbExclamation, "Student Entry"
        Me.lastnametxtb.SetFocus
        Exit Sub
    End If

    If Me.pointstxtb.Value = "" Then
        MsgBox "Please enter points achieved.", vbExclamation, "Student Entry"
        Me.pointstxtb.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.pointstxtb.Value) Then
        MsgBox "The Points box must contain a number.", vbExclamation, "Student Entry"
        Me.pointstxtb.SetFocus
        Exit Sub
    End If
    With Sheet1
        R = .Cells(Rows.Count, 1).End(xlUp).Row + 1
'add from form
        .Cells(R, 1).Value = Me.firstnametxtb.Value
        .Cells(R, 2).Value = Me.lastnametxtb.Value
        .Cells(R, 3).Value = Me.pointstxtb.Value
        .Cells(R, 3).NumberFormat = "000"
        'etc
    End With
End Sub

Add a button from the Controls Toolbox then use

Addnew.Show

[jermaindefoe]

.Cells(R, 3).NumberFormat = "000"

this still doesnt convert it????