Spremely Frustrated with a 'runtime error: "-2147417848 (80010108)" Method "Value of "Range" Failed'

[SGD78]

Hi everyone,

I'm hoping I can get some assistance with this issue. I've designed a UserForm to easily facilitate maintaining some records in a table.

Everything was working wonderfully until I added some Search features, then my Add button suddenly stopped functioning and started crashing Excel with a Runtime Error: "-2147417848 (80010108)" Method "Value of "Range" Failed'.

Any helpful assistance would be greatly appreciated.

My "Add" code is as follows:
(The error occurs on the line in orange which is also underlined)

Private Sub cmdbtn_Add_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Client Database")
    Dim NextRow As String
        'find the next empty row
    NextRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        'Verify That Form-Data Is Complete
        If Me.cmbx_CustomerName.Value = "" Then
            MsgBox "Please Enter The Customer's Name!"
            Me.cmbx_CustomerName.SetFocus
            Exit Sub
        End If
        If Me.txtbx_CityState.Value = "" Then
            MsgBox "Please Enter City & State!"
            Me.txtbx_CityState.SetFocus
            Exit Sub
        End If
        If Me.txtbx_AuditDate.Value = "" Then
            MsgBox "Please Enter Audit Date!"
            Me.txtbx_AuditDate.SetFocus
            Exit Sub
        End If
        If Me.cmbx_AuditorsInitials.Value = "" Then
            MsgBox "Please Enter Auditor's Initials!"
            Me.cmbx_AuditorsInitials.SetFocus
            Exit Sub
        End If
        If Me.txtbx_WebAddress.Value = "" Then
            MsgBox "Please Enter Client's Web Address!"
            Me.txtbx_WebAddress.SetFocus
            Exit Sub
        End If
            'Transfer Data
            Cells(NextRow, 1).Value = Me.cmbx_CustomerName.Text
            Cells(NextRow, 2).Value = Me.txtbx_CityState.Text
            'Identify if customer is active
            If Me.optbtn_ActiveYes = True Then
                Cells(NextRow, 3).Value = "Active"
            End If
            If Me.optbtn_ActiveNo = True Then
                Cells(NextRow, 3).Value = "Inactive"
            End If
            'Identify if customer is currently paying
            If Me.optbtn_Contract = True Then
                Cells(NextRow, 4).Value = "Y"
            End If
            If Me.optbtn_AddOn = True Then
                Cells(NextRow, 4).Value = "N"
            End If
            'Identify readiness level
            If Me.optbtn_ReadyPoor = True Then
                Cells(NextRow, 5).Value = "Poor"
            End If
            If Me.optbtn_ReadyFair = True Then
                Cells(NextRow, 5).Value = "Fair"
            End If
            If Me.optbtn_ReadyModerate = True Then
                Cells(NextRow, 5).Value = "Moderate"
            End If
            If Me.optbtn_ReadyGood = True Then
                Cells(NextRow, 5).Value = "Good"
            End If
            If Me.optbtn_ReadyExcellent = True Then
                Cells(NextRow, 5).Value = "Excellent"
            End If
            'Identify if client has deflection setup
            If Me.optbtn_DeflectYes = True Then
                Cells(NextRow, 6).Value = "Y"
            End If
            If Me.optbtn_DeflectNo = True Then
                Cells(NextRow, 6).Value = "N"
            End If
            'Identify if deflection is working properly
            If Me.optbtn_DeflectPass = True Then
                Cells(NextRow, 7).Value = "Pass"
            End If
            If Me.optbtn_DeflectFail = True Then
                Cells(NextRow, 7).Value = "Fail"
            End If
            Cells(NextRow, 8).Value = Me.txtbx_AuditDate.Text
            Cells(NextRow, 9).Value = Me.cmbx_AuditorsInitials.Text
            If Me.chbx_Sensitive = True Then
                Cells(NextRow, 10).Value = "Y"
            ElseIf Me.chbx_NotSensitive = True Then
                Cells(NextRow, 10).Value = "N"
            End If
            Cells(NextRow, 11).Value = Me.txtbx_WebAddress.Text
        Application.EnableEvents = True
    Unload Me
End Sub

Thanks in advance for any useful help with resolving this issue.

SGD78

 

[SGD78]

Always a pleasure to beat a tough one....glad to help...

BTW....what is all the mousehook code for ?? it doesn't seem ncessary !!

The MouseHook Code (found in Sheet1 SelectionChange code) is for the purpose of using the mouse-wheel to scroll through the records (in the ComboBoxes Customer Name & Auditor's Initials) ... (It's actually very helpful) ...

Thanks again for all your efforts and input ... Have a great weekend and a Happy Easter ...

 

[wornhall]

change "Dim NextRow As String" to "Dim NextRow As Long
Integer has a max of roughly +32,000, at which point you get an error.
Depending on your version of the Excel editor, integer may automatically be converted to long,
though you would not see it except in the binary of your code.
The upshot of this is that integer is obsolete.