Error handling in Userform

[Derick63]

Hello all.

I created a payroll system in Excel and use a userform to enter employee data. See image below

Named controls in order shown:
Reg1 (DTPicker)
TextBox1 (TextBox)
Reg2 (ComboBox)
Reg3 (TextBox)
Reg4 (TextBox)
Reg5 (TextBox)
Reg6 (TextBox)

When I click on the 'Add To Sheet' button it checks for an employee's name in Reg2 (Employee). If it's empty, a message appears.
Here's my issue; When I select 'OK' in the MsgBox the MsgBox disappears normally but code continues to add the data to the respective locations without the employee's name.

I need a piece of code to halt the 'Add To Sheet' process or disable the button until Reg2 is populated. Thanks in advance for your help

Here's the code for the 'Add To Sheet' button:

Private Sub CmdAdd_Click()    Dim sht As String
    Dim nextrow As Range


    'set the variable for the sheets
    sht = TextBox1.Value
    
    'check for Employee name
    If Trim(Me.Reg2.Value) = "" Then
      Me.Reg2.SetFocus
      MsgBox "Please select an Employee"
    End If


    'add the data to the selected worksheet
    Set nextrow = Sheets(sht).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
        nextrow = Me.Controls("Reg1").Value
        Set nextrow = nextrow.Offset(0, 1)
        nextrow = Me.Controls("Reg2").Value
        Set nextrow = nextrow.Offset(0, 1)
        nextrow = Me.Controls("Reg3").Value
        Set nextrow = nextrow.Offset(0, 1)
        nextrow = Me.Controls("Reg4").Value
        Set nextrow = nextrow.Offset(0, 4)
        nextrow = Me.Controls("Reg5").Value
        Set nextrow = nextrow.Offset(0, 1)
        nextrow = Me.Controls("Reg6").Value
        


    'clear the values in the userform
    Me.Reg2.Value = ""
    Me.Reg3.Value = ""
    Me.Reg4.Value = ""
    Me.Reg5.Value = ""
    Me.Reg6.Value = ""


    'communicate the results
    MsgBox "The values have been sent to the " & sht & " sheet"


End Sub

 

[Derick63]

Hi Dave,
All is done and went into testing mode. Everything worked great........ Until I protected the sheets to write to. I want to put Sheets(sht).Unprotect before adding the values and Sheets(sht).Protect after adding the values but I don't know where to insert these lines. Can you tell me where to insert them? Thanks.

Derick.

 

[dmt32]

Private Sub CmdAdd_Click()
    Dim sht As Worksheet
    Dim nextrow As Range
    Dim i As Integer, c As Integer
    
'turn error handling on
    On Error GoTo myerror
'set the variable for the sheets
    Set sht = ThisWorkbook.Worksheets(TextBox1.Value)
    
    sht.Unprotect Password:=""
    
'check for Employee name
    If Trim(Me.Reg2.Value) = "" Then
        Me.Reg2.SetFocus
        MsgBox "Please select an Employee", 48, "Entry Required"
    Else
'next blank row
        Set nextrow = sht.Cells(sht.Rows.Count, 2).End(xlUp).Offset(1, 0)
        c = 0
        For i = 1 To 6
            With Me.Controls("Reg" & i)
'add the data to the selected worksheet
                nextrow.Offset(, c).Value = .Value
'clear the values in the userform
               If i > 1 Then .Value = ""
            End With
'next column
            c = c + 1
'move to column 9 ("I")
            If c = 4 Then c = c + 3
        Next i
    End If
        
    sht.Protect Password:=""
    
myerror:
    If Err <> 0 Then
'something went wrong
        MsgBox (Error(Err)), 48, "Error"
     Else
            
'communicate the results
        MsgBox "The values have been sent to the " & sht.Name & " sheet", 64, "Record Saved"
        Me.Reg1.SetFocus
    End If
End Sub

add password as required.

Dave

 

[Derick63]

Perfect! Thanks Dave ray: