userform on VBA

[kelvin_9]

i have a userform to record my employee info, but i found something error sometimes when i enter a new employee.
for instance, i save Leon Wong as the latest in row 6, but column C and so on will interrupt into another row like row 3.
here is my code and where am i stuck with?

thanks

    Dim nextrow As Range    'error handler
    On Error GoTo errHandler:
    'set the next row in the database
    Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    'check for values in the first 4 controls
    For X = 1 To 4
        If Me.Controls("Reg" & X).Value = "" Then
            MsgBox "You must add all data"
            Exit Sub
        End If
    Next
    'check for duplicate payroll numbers
    If WorksheetFunction.CountIf(Sheet2.Range("D:D"), Me.reg4.Value) > 0 Then
        MsgBox "This cast member already exists"
        Exit Sub
    End If
    'number of controls to loop through
    cNum = 13
    'add the data to the database
    For X = 1 To cNum
        nextrow = Me.Controls("Reg" & X).Value
        Set nextrow = nextrow.Offset(0, 1)
    Next
    'clear the controls
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
    'sort the database
    Sortit
    'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"

 

[dmt32]

Hi,
untested but see if this update to your code helps

Private Sub CommandButton1_Click()
    Dim nextrow As Range
    Dim X As Integer
    Dim arr() As Variant
    
'number of controls to loop through
    Const cNum As Integer = 13
    
    ReDim arr(1 To cNum)
    
 'error handler
    On Error GoTo errHandler:
    
'check for values in the first 4 controls
    For X = 1 To cNum
        With Me.Controls("Reg" & X)
            If X < 5 And .Value = "" Then
                .SetFocus
                MsgBox "You must add all data", 48, "Entry Required"
                Exit Sub
            End If
            arr(X) = .Value
        End With
    Next X
        
'check for duplicate payroll numbers
        If WorksheetFunction.CountIf(Sheet2.Range("D:D"), arr(4)) > 0 Then
            MsgBox "This cast member already exists", 16, "Member Exists"
            Exit Sub
        End If
        
'set the next row in the database
        Set nextrow = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
'add record to worksheet
        nextrow.Resize(, cNum).Value = arr
        
'clear the controls
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
        
'sort the database
SortIt
'error block
        On Error GoTo 0
        Exit Sub
errHandler:
        MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
        & Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "Please notify the administrator"
End Sub

Note: Your image shows only 4 fields of data yet your code loops thru 13 controls - I trust this is correct? If not, adjust the Const Variable value shown in red as required.

Dave

 

[Fluff]

If you remove the the line in red does it still put information in the wrong rows

    Next
    'sort the database
    [COLOR=#ff0000]Sortit[/COLOR]
    'error block
    On Error GoTo 0
    Exit Sub

 

[kelvin_9]

Hi,
untested but see if this update to your code helps

Private Sub CommandButton1_Click()
    Dim nextrow As Range
    Dim X As Integer
    Dim arr() As Variant
    
'number of controls to loop through
    Const cNum As Integer = 13
    
    ReDim arr(1 To cNum)
    
 'error handler
    On Error GoTo errHandler:
    
'check for values in the first 4 controls
    For X = 1 To cNum
        With Me.Controls("Reg" & X)
            If X < 5 And .Value = "" Then
                .SetFocus
                MsgBox "You must add all data", 48, "Entry Required"
                Exit Sub
            End If
            arr(X) = .Value
        End With
    Next X
        
'check for duplicate payroll numbers
        If WorksheetFunction.CountIf(Sheet2.Range("D:D"), arr(4)) > 0 Then
            MsgBox "This cast member already exists", 16, "Member Exists"
            Exit Sub
        End If
        
'set the next row in the database
        Set nextrow = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
'add record to worksheet
        nextrow.Resize(, cNum).Value = arr
        
'clear the controls
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
        
'sort the database
SortIt
'error block
        On Error GoTo 0
        Exit Sub
errHandler:
        MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
        & Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "Please notify the administrator"
End Sub

Note: Your image shows only 4 fields of data yet your code loops thru 13 controls - I trust this is correct? If not, adjust the Const Variable value shown in red as required.

Dave

If you remove the the line in red does it still put information in the wrong rows

    Next
    'sort the database
    [COLOR=#ff0000]Sortit[/COLOR]
    'error block
    On Error GoTo 0
    Exit Sub

thank you for the reply, Fluff's works great and simply sloved


sorry for my delay, it's work smooth and great with correct row under my expectation
i've another question IF i need edit(means update) my employee info, i can simply search in listbox and double click the employee for editing.
however, sometimes turns to error #91 . here is my code

    Dim findvalue As Range
    'error handling
    On Error GoTo errHandler:
    'check for values
    If reg1.Value = "" Or reg2.Value = "" Then
        MsgBox "There is no data to edit"
        Exit Sub
    End If
    'edit the row
    Set findvalue = Sheet2.Range("D:D").Find(What:=reg4, LookIn:=xlValues).Offset(0, -3)
    'if the edit is a name then add it
    Me.reg3.Value = Me.reg1.Value + ", " + Me.reg2.Value
    
    For X = 1 To cNum
        findvalue = Me.Controls("Reg" & X).Value
        Set findvalue = findvalue.Offset(0, 1)
    Next
    'refresh the listbox
    Lookup
    'error block
    On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & _
           "The error number is:  " & Err.Number & vbCrLf & _
           Err.Description & vbCrLf & "Please notify the administrator"

 

[Fluff]

As this is a totally different question, please start a new thread.
Thanks

 

[kelvin_9]

As this is a totally different question, please start a new thread.
Thanks

understood & apologize for the inconvenience