valenza
01-08-2019, 08:59 AM
Hi,
I am new to VBA and am trying to set up some user forms for people to use so I can collect monthly data.
I followed a tutorial online (https://www.youtube.com/watch?v=FB_pm9wt6jI) and added to my spreadsheet an Add New Record, Edit Record, and Delete Record button.
How I have set up the edit record entry form is that you select the date from a dropdown list dictated by previous entries and then edit the rest of the data. However, I keep getting an error message saying 'Run-time error 13: Type mismatch'.
When I run the debug it highlights a specific line (in red). Code is below, would appreciate any help! And let me know if i need ot attach anything else! THANK YOU!
"
Private Sub ComboBox1_Change()
If Me.ComboBox1.Value <> "" Then
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim i As Integer
i = Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("A:A"), 0)
Me.TextBox2.Value = sh.Range("B" & i).Value
Me.TextBox3.Value = sh.Range("C" & i).Value
Me.TextBox4.Value = sh.Range("D" & i).Value
Me.TextBox5.Value = sh.Range("E" & i).Value
Me.TextBox6.Value = sh.Range("F" & i).Value
Me.TextBox7.Value = sh.Range("G" & i).Value
Me.TextBox8.Value = sh.Range("H" & i).Value
End If
End Sub
Private Sub CommandButton1_Click()
'''''''''' Validation ''''''''''
If Me.TextBox2.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox5.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox6.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox7.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox8.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
'''''''''' check duplicate Date
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim n As Long
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
sh.Unprotect "1234"
sh.Range("A" & n).Value = Me.ComboBox1.Value
sh.Range("B" & n).Value = Me.TextBox2.Value
sh.Range("C" & n).Value = Me.TextBox3.Value
sh.Range("D" & n).Value = Me.TextBox4.Value
sh.Range("E" & n).Value = Me.TextBox5.Value
sh.Range("F" & n).Value = Me.TextBox6.Value
sh.Range("G" & n).Value = Me.TextBox7.Value
sh.Range("H" & n).Value = Me.TextBox8.Value
sh.Protect "1234"
Me.ComboBox1.Value = " "
Me.TextBox2.Value = " "
Me.TextBox3.Value = " "
Me.TextBox4.Value = " "
Me.TextBox5.Value = " "
Me.TextBox6.Value = " "
Me.TextBox7.Value = " "
Me.TextBox8.Value = " "
MsgBox "New audit record has been added", vbInformation
End Sub
Private Sub CommandButton2_Click()
Me.ComboBox1.Value = " "
Me.TextBox2.Value = " "
Me.TextBox3.Value = " "
Me.TextBox4.Value = " "
Me.TextBox5.Value = " "
Me.TextBox6.Value = " "
Me.TextBox7.Value = " "
Me.TextBox8.Value = " "
End Sub
Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim i As Integer
Me.ComboBox1.Clear
Me.ComboBox1.AddItem ""
For i = 4 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox1.AddItem sh.Range("A" & i).Value
Next i
End Sub
"
I am new to VBA and am trying to set up some user forms for people to use so I can collect monthly data.
I followed a tutorial online (https://www.youtube.com/watch?v=FB_pm9wt6jI) and added to my spreadsheet an Add New Record, Edit Record, and Delete Record button.
How I have set up the edit record entry form is that you select the date from a dropdown list dictated by previous entries and then edit the rest of the data. However, I keep getting an error message saying 'Run-time error 13: Type mismatch'.
When I run the debug it highlights a specific line (in red). Code is below, would appreciate any help! And let me know if i need ot attach anything else! THANK YOU!
"
Private Sub ComboBox1_Change()
If Me.ComboBox1.Value <> "" Then
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim i As Integer
i = Application.Match(VBA.CLng(Me.ComboBox1.Value), sh.Range("A:A"), 0)
Me.TextBox2.Value = sh.Range("B" & i).Value
Me.TextBox3.Value = sh.Range("C" & i).Value
Me.TextBox4.Value = sh.Range("D" & i).Value
Me.TextBox5.Value = sh.Range("E" & i).Value
Me.TextBox6.Value = sh.Range("F" & i).Value
Me.TextBox7.Value = sh.Range("G" & i).Value
Me.TextBox8.Value = sh.Range("H" & i).Value
End If
End Sub
Private Sub CommandButton1_Click()
'''''''''' Validation ''''''''''
If Me.TextBox2.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox5.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox6.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox7.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
If Me.TextBox8.Value = "" Then
MsgBox "Please enter number of audits completed, if none enter 0.", vbCritical
Exit Sub
End If
'''''''''' check duplicate Date
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim n As Long
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
sh.Unprotect "1234"
sh.Range("A" & n).Value = Me.ComboBox1.Value
sh.Range("B" & n).Value = Me.TextBox2.Value
sh.Range("C" & n).Value = Me.TextBox3.Value
sh.Range("D" & n).Value = Me.TextBox4.Value
sh.Range("E" & n).Value = Me.TextBox5.Value
sh.Range("F" & n).Value = Me.TextBox6.Value
sh.Range("G" & n).Value = Me.TextBox7.Value
sh.Range("H" & n).Value = Me.TextBox8.Value
sh.Protect "1234"
Me.ComboBox1.Value = " "
Me.TextBox2.Value = " "
Me.TextBox3.Value = " "
Me.TextBox4.Value = " "
Me.TextBox5.Value = " "
Me.TextBox6.Value = " "
Me.TextBox7.Value = " "
Me.TextBox8.Value = " "
MsgBox "New audit record has been added", vbInformation
End Sub
Private Sub CommandButton2_Click()
Me.ComboBox1.Value = " "
Me.TextBox2.Value = " "
Me.TextBox3.Value = " "
Me.TextBox4.Value = " "
Me.TextBox5.Value = " "
Me.TextBox6.Value = " "
Me.TextBox7.Value = " "
Me.TextBox8.Value = " "
End Sub
Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Audits")
Dim i As Integer
Me.ComboBox1.Clear
Me.ComboBox1.AddItem ""
For i = 4 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox1.AddItem sh.Range("A" & i).Value
Next i
End Sub
"