Data Entry VBA Form

[valenza]

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
"

[Kenneth Hobs]

Welcome to the forum!

CLng() means convert to Long type. You Dimmed i as Integer. Change it to Long...

[valenza]

Thank you and hello!

I tried what you said and it still gives the same error, and highlights the same bit of code.

To clarify I've changed

Dim i As Integer

to

​Dim i As Long

[valenza]

All good figured it out!!! Thanks for all your help!