userform on VBA

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
10f629c2d1


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

Code:
    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"
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
untested but see if this update to your code helps

Rich (BB code):
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
 
Last edited:
Upvote 0
If you remove the the line in red does it still put information in the wrong rows
Code:
    Next
    'sort the database
    [COLOR=#ff0000]Sortit[/COLOR]
    'error block
    On Error GoTo 0
    Exit Sub
 
Upvote 0
Hi,
untested but see if this update to your code helps

Rich (BB code):
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
Code:
    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
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"
 
Upvote 0
As this is a totally different question, please start a new thread.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top