PDA

View Full Version : [SOLVED] Type Mismatch Error



Chunk
01-31-2017, 10:53 AM
Hi guys and gals,
Here is my issue. I have a userform with many text boxes that read and write data to a worksheet. I want the user to be able to delete data from the userform text boxes, then when the “submit” button is clicked, any data will be removed from the appropriate cells. As long as there is data in the textboxes, the code works fine when the submit button is clicked. When there isn’t any data in the text boxes, I receive a Type Mismatch error. Here is the code I am working with.


Private Sub btn_Update_Click()
Call mod_Data_Change.Update
End Sub




Sub Update()
Dim r As Long
Dim WB As Workbook
Dim ws As Worksheet
Dim FindString As String
Dim Rng As Range
Dim Err As Variant

Set ws = ThisWorkbook.Worksheets(UserForm2.cbo_project.Value)

Worksheets(UserForm2.cbo_project.Value).Activate

FindString = UserForm2.cb_DDate.Value

If Trim(FindString) <> "" Then
Set Rng = ws.Range("L:L").Find( _
What:=CDate(FindString), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not Rng Is Nothing Then
If UserForm2.tb_Totcertplot.Text And UserForm2.tb_Totcert.Text And UserForm2.tb_totcertrem.Text = "" Then
Rng(1, 19) = UserForm2.tb_Totcert.Text
Rng(1, 20) = UserForm2.tb_Totcertplot.Text
Rng(1, 21) = UserForm2.tb_totcertrem.Text

Else
If UserForm2.tb_Totcertplot.Value - UserForm2.tb_Totcert.Value - UserForm2.tb_totcertrem.Value = 0 Then
UserForm2.Frame14.BackColor = &H8000000F
UserForm2.Frame15.BackColor = &H8000000F
Rng(1, 19) = UserForm2.tb_Totcert.Text
Rng(1, 20) = UserForm2.tb_Totcertplot.Text
Rng(1, 21) = UserForm2.tb_totcertrem.Text
If UserForm2.tb_Totcertplot.Value <> 0 Then
Rng(1, 23) = UserForm2.tb_totcertrem.Value / UserForm2.tb_Totcertplot.Value
End If
UserForm2.tb_Totcrtremprct.Text = Rng(1, 23)
UserForm2.tb_Totcrtremprct.Value = Format(UserForm2.tb_Totcrtremprct.Value, "0.0%")
Else
UserForm2.Frame14.BackColor = &H8080FF
UserForm2.Frame15.BackColor = &H8080FF
End If
End If

" If UserForm2.tb_Totcertplot.Text And UserForm2.tb_Totcert.Text And UserForm2.tb_totcertrem.Text = "" Then"
Is where the code fails…..any ideas?

Thanks in advance,

Chunk

Paul_Hossler
01-31-2017, 11:35 AM
Maybe





If UserForm2.tb_Totcertplot.Text ="" And UserForm2.tb_Totcert.Text ="" And UserForm2.tb_totcertrem.Text = "" Then

Chunk
02-01-2017, 06:08 AM
Paul,

That worked beautifully. Thank you for the timely response.

Chunk