Derick63

New Member
Joined
Apr 6, 2018
Messages
39
Hi all.
I have a userform with 3 TextBoxes (ones having an issue anyway) to adjust the value in the respective cells on an Employee Information worksheet. The Balance is shown as a label.Caption using Application.VlookUp for the employee shown in a ComboBox called Reg2.
Reg9, Reg10 and Reg11 TextBox is where the user would enter the value of Vacation Days used, Sick Leave used and Loan Repayment to adjust the balance.


Here's my issue: When I enter values in Reg9, 10 & 11 and click on the Add To Sheet CommandButton it works great but, if they're any blanks in any of the three TextBoxes I get a Type Mismatch Error. What I'd like to happen is if the .value of the worksheet cell is blank then cancel the math. I don't know how to cancel the procedure using the with in there
Here's where I am now: Scroll to the **** section.


Code:
Private Sub CmdAdd_Click()
    'Button To Add Reg1 through Reg9 Values to Worksheet (sht)
    
    Dim Ctrl As Control
    Dim DT As Date
    Dim sht As Worksheet
    Dim nextrow As Range
    Dim i As Integer, c As Integer


    Application.ScreenUpdating = False
    
'turn error handling on
    On Error GoTo myerror
    
'set the variable for the sheets
    Set sht = ThisWorkbook.Worksheets(TextBox1.Value)
    
'set variable for the Date
    DT = DateValue(Me.Reg1.Value)


'unprotect sheet posting to
    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 9
            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 > 9 Then .Value = ""
            End With
'next column
            c = c + 1
'move to next entry column
            If c = 7 Then c = c + 4
        Next i
    End If


'****************** My Issue is below here ******************

'Vacation Days Balance Adjustment
    With Sheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 9)
    .Value = .Value - Reg10.Value
    End With
    
'Sick Leave Days Balance Adjustment
    With Sheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 11)
    .Value = .Value - Reg11.Value
    End With
    
'Loan Balance reduction on Employee Information sheet
'Loan Repayment goes to Misc Deductions on posting sheet
    With Sheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 13)
    .Value = .Value - Reg9.Value
    End With

'****************** My Issue is above here ******************


'clear out the values after posting to sheet
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
        If TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
        If TypeName(Ctrl) = "Label" Then Ctrl.Value = ""


    Next Ctrl


'Repopulate TextBox1 Value
    Me.TextBox1.Value = Format(DT - 4, "mmmm")


'position cursor in the employees name box
    Me.Reg2.SetFocus
    
'protect sheet posting to
    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.Reg2.SetFocus
        End If
    Application.ScreenUpdating = True


End Sub


I also tried this and got the same result "Type Mismatch"
Code:
'*******************
'Vacation Days Balance Adjustment
    Dim vb As Integer
    vb = ThisWorkbook.Worksheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 9).Value
        If vb > 0 Then
        vb = vb - Reg10.Value
        End If
        '.Value = .Value - Reg10.Value
    'End With
    
'Sick Leave Days Balance Adjustment
    Dim sl As Integer
    sl = ThisWorkbook.Worksheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 11).Value
        If sl > 0 Then
        sl = sl - Reg11.Value
        End If
        '.Value = .Value - Reg11.Value
    'End With


'Loan Balance reduction on Employee Information sheet
'Loan Repayment goes to Misc Deductions on posting sheet
    Dim lb As Integer
    lb = ThisWorkbook.Worksheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 13).Value
        If lb > 0 Then
        lb = lb - Reg9.Value
        End If
        '.Value = .Value - Reg9.Value
    'End With
'*******************
 

Derick63

New Member
Joined
Apr 6, 2018
Messages
39
Exactly Norie. I was missing step 4. I realized that and fixed it in my last post.

Thanks again.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,118,698
Messages
5,573,689
Members
412,548
Latest member
wallisonlac
Top