Type Mismatch issue

[Derick63]

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.

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"

'*******************
'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
'*******************

 

[Norie]

Try converting the text values in the textboxes to numeric values using one of the conversion functions like Val, CInt, CLng etc.

 

[Derick63]

Try converting the text values in the textboxes to numeric values using one of the conversion functions like Val, CInt, CLng etc.

Thanks, I'll do that and report back.

 

[Derick63]

Try converting the text values in the textboxes to numeric values using one of the conversion functions like Val, CInt, CLng etc.

Hello Norie,
No matter what I do I'm still getting the Type Mismatch. I'm relatively new to VBA but know enough to get in trouble and learning as I go. The issue is that the three textboxes will be left blank the majority of the time for each employees entry and that's where the issue is. I'd like to check if the textbox is blank before doing the calculation. When there's an entry it works as it supposed to but not when it's blank.
What code can I insert to check if it's blank and, if it is, exit the if statement and move on to the next one?

Here's what I tried just now and it still gave me a Type Mismatch:

Private Sub CmdAdd_Click()
Dim ufvb As Integer, wsvb As Integer, ufsl As Integer, wssl As Integer, uflb As Integer, wslb As Integer


'Vacation Days Balance Adjustment
    ufvb = Reg10.Value 'userform TextBox value
    wsvb = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O50"), 10, False) 'Worksheet Lookup Value
    If ufvb = "" Then
    Exit Sub
    Else
    wsvb = wsvb - ufvb
    End If
    
'Sick Leave Days Balance Adjustment
    ufsl = Reg11.Value 'userform TextBox value 
    wssl = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O50"), 12, False) 'Worksheet Lookup Value
    If ufsl = "" Then
    Exit Sub
    Else
    wssl = wssl - ufsl
    End If
        
'Loan Balance Adjustment
    uflb = Reg9.Value 'userform TextBox value
    wslb = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O50"), 14, False) 'Worksheet Lookup Value
    If uflb = "" Then
    Exit Sub
    Else
    wslb = wslb - uflb
    End If


End Sub

Thanks for your effort.

 

[Derick63]

Hello All,
So this is what I did and it worked. It may be rudimentary but with a lack of coding experience I had to get creative.
I kept the original code:

 '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

I changed the textbox property: Text: 0 (Zero) for the three textboxes
I also added three lines to repopulate the textboxes with 0's (Zeros) after clicking the CommandButton, which also clears all data from all textboxes on the userform for the next employee)
Me.Reg9.Value = "0"
Me.Reg10.Value = "0"
Me.Reg11.Value = "0"

This may be a quick fix but I'm very interested in how this can be avoided or what code would be used to avoid this Type Mismatch issue. I've searched all over but can't find an explanation or code.

Thank you for reading

 

[Derick63]

To Whomever is reading or following this post, I got help from Debra Dalgleish of Contextures.com on the proper way to cancel a VBA math function if a userform textbox is empty.

I'm grabbing a value from a cell on a worksheet and assigning it to a variable then subtracting an entered value in the textbox Reg10 from it and reposting the result to the same cell. If the textbox is blank it won't give me a "Type Mismatch" error anymore.

Dim VacUsed As Long, VacBal As Long


On Error Resume Next


    VacUsed = Reg10.Value 
    VacBal = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O100"), 10, False) 'Vacation Days Balance for an employee shown in Reg2 Combobox


On Error GoTo 0


    VacBal = VacBal - VacUsed

hope this helps anyone.

 

[Norie]

Did you try using one of the conversion functions I suggested?

For example Val which will return 0 when passed an empty string.

Dim VacUsed As Long, VacBal As Variant

    VacUsed = Val(Reg10.Value)
    VacBal = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O100"), 10, False) 'Vacation Days Balance for an employee shown in Reg2 Combobox


    If Not IsError(VacBal) Then 
        VacBal = VacBal - VacUsed
    End If

P.S. You might also want to consider checking if the textbox is empty.

If Reg10.Value = "" Then 
    MsgBox "Reg10 textbox is empty." ' replace with appropriate message
    Exit Sub
End If

 

[Derick63]

Hi Nori,
I so appreciate your help with this. Your code didn't work and I think this is why..... and I could be so incorrect too

Lets assume the following;
VacBal = 5 (per Application.Vlookup)
VacUsed = 1 (user entered)

When the process reaches the line VacBal = VacBal - VacUsed, that'll mean 5 = 5 - 1. When I tried your suggestion the worksheet cells did not change

What I'm trying to accomplish is the following:

1. Get the vacation balance value for the employee in Reg2.Value using Application.Vlookup (Result: VacBal = 5)
2. Get the cell address for the Vacation Balance of the employee in Reg2 and assign it to variable, let's say NewVacBal
3. Get the Reg10.Value (Result: VacUsed = 1)
4. Then Subtract; VacBal - VacUsed and pass that new value back to the Worksheet cell where VacBal came from.
5. If the VacUsed.Value is blank, do nothing.

And the kicker is most of the times the cell will be blank unless the employee took a vacation day or was out sick. This is where the Type Mismatch happens, when it's left blank.

I'm looking into ControlSource as I type this. I know nothing about it so at the least I'll know a bit more on it and what it does. Learning is fun!

Thanks again Norie

 

[Norie]

I think the problem is that you are missing step 4, i.e. writing the adjusted value back to the sheet.

To do that you would need to locate the cell that you want to write back as well as getting it's value for the calculation.

PS ControlSource is probably not a good idea.

 

[Derick63]

Hi again Nori,

Well, ControlSource wasn't the way to go so I played around some more and I got it to do exactly as I wanted it to do. Here's the code with your Val suggestion

Dim VacUsed As Long, VacBal As Long
Dim NewVacBal As Range


Set NewVacBal = Sheets("Employee Information").Columns("B").Find(Reg2.Value).Offset(, 9)   'Column "B" is employees names and Offset (,9) is where the Vacation Balance resides


    VacUsed = Val(Reg8.Value)
    VacBal = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:O100"), 10, False) 'Vacation Days Balance for an employee shown in Reg2 Combobox
    If Not IsError(VacUsed) Then
        NewVacBal = VacBal - VacUsed 
    End If