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

 

[dmt32]

Hi,
After your msgbox closes you need to exit the sub

'check for Employee name
    If Trim(Me.Reg2.Value) = "" Then
      Me.Reg2.SetFocus
      MsgBox "Please select an Employee"
[COLOR=#ff0000]      Exit Sub[/COLOR]
     End If

Dave

 

[Derick63]

Hi,
After your msgbox closes you need to exit the sub

'check for Employee name
    If Trim(Me.Reg2.Value) = "" Then
      Me.Reg2.SetFocus
      MsgBox "Please select an Employee"
[COLOR=#ff0000]      Exit Sub[/COLOR]
     End If

Dave

Dave, Thanks a lot. Couldn't believe it was that simple. Still learning.....

Derick

 

[dmt32]

Dave, Thanks a lot. Couldn't believe it was that simple. Still learning.....

Derick

Your welcome – not matter how long you have been doing it for, you never stop learning & MrExcel a good resource for shared knowledge and the different approaches (programming style) taken by contributors to solving a problem.

For interest, you can eliminate most of these code lines

'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 = ""

By placing in a For Next Loop

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)
    
'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
                .Value = ""
            End With
'next column
            c = c + 1
'move to column 9 ("I")
            If c = 4 Then c = c + 3
        Next i
    End If
        
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

Not tested but should do the same thing.

Hope Helpful

Dave

 

[Derick63]

Wow! Thank you Dave. See? That's the difference between a newbie and someone with experience. I wrote my code as a readable process that I, an inexperienced VBA student, could understand. My whole project could probably be rewritten a lot more efficiently as you have done with that code.
Going to test your code now. Thanks again.

Derick

 

[Derick63]

Dave,
your code produced an error message box stating "Can't set Value to NULL when CheckBox property=FALSE". It halted the process when I clicked on OK. No probs, I put my code back in until....

Derick

 

[dmt32]

thanks but you will find another here would probably write it differently. I normally tend to use arrays to write data to worksheets but one step at a time.
In general aim should be to try & make your coding as efficient as possible, always declare variables with correct data type, qualify the ranges to the worksheet & employ error handling when needed all of which is a good start.

Good luck


Dave

 

[dmt32]

Dave,
your code produced an error message box stating "Can't set Value to NULL when CheckBox property=FALSE". It halted the process when I clicked on OK. No probs, I put my code back in until....

Derick

There was no Checkbox listed in your post?

I suspect error maybe the Data Picker

Add line shown in RED & see if resolves

'clear the values in the userform
               If i > 1 Then .Value = ""

Dave

 

[Derick63]

There was no Checkbox listed in your post?

I suspect error maybe the Data Picker

Add line shown in RED & see if resolves

'clear the values in the userform
               If i > 1 Then .Value = ""

Dave

Dave, That worked in a weird way. The MsgBox popped up as coded "Please select an Employee" but when I hit OK the other MsgBox "The values have been sent to the " & sht & " sheet" still showed but the good thing is that it was never posted to the destination sheet.

On another note: the new code showed me I had an error in my code that was wiping out two entries.
'move to column 9 ("I")
If c = 4 Then c = c + 3
Next i
I need to enter the last two values for that row into to column 6 ("G") and column 7 ("H"). So I changed it to "If c = 4 Then c = c + 1"

I so do appreciate your help. I'm learning, I'm learning.

Derick

 

[dmt32]

Dave, That worked in a weird way. The MsgBox popped up as coded "Please select an Employee" but when I hit OK the other MsgBox "The values have been sent to the " & sht & " sheet" still showed but the good thing is that it was never posted to the destination sheet.

On another note: the new code showed me I had an error in my code that was wiping out two entries.
'move to column 9 ("I")
If c = 4 Then c = c + 3
Next i
I need to enter the last two values for that row into to column 6 ("G") and column 7 ("H"). So I changed it to "If c = 4 Then c = c + 1"

I so do appreciate your help. I'm learning, I'm learning.

Derick

I just based on what was published but as always, posted suggestions should be updated by OP to meet specific project and as you say, learn.


All best

Dave