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

 

[wornhall]

A tiny nit: Next row is defined as a string, but it is a number. Double is good.

No orange or underlining here, either.

It is also noticeable that a lot of data is posted to NextRow even though one or several entries are missing, and they seem to be key.

 

[SGD78]

A tiny nit: Next row is defined as a string, but it is a number. Double is good.

No orange or underlining here, either.

It is also noticeable that a lot of data is posted to NextRow even though one or several entries are missing, and they seem to be key.

The orange and underlining was in the second posting of my code (my apologies) ...

Could you perhaps elaborate on what you mean by "It is also noticeable that a lot of data is posted to NextRow even though one or several entries are missing, and they seem to be key." ?

Every column (from 1 to 11) has a defined value in both the userform, and destination location in the table, so I'm admittedly a bit confused by your statement ...

 

[Michael M]

Can you share the workbook ??
If so, Upload to dropbox or similar and post the link back here

 

[SGD78]

Can you share the workbook ??
If so, Upload to dropbox or similar and post the link back here

Sure ... Give just a sec though because some of the data is confidential and will have to be amended first ...

 

[SGD78]

Ok ... Sorry about the delay, but the amendments have been made, and the file can be found here:

https://www.dropbox.com/s/pvy1wz8b1e11srx/UserForm ADD Issues.xlsm?dl=0

 

[SGD78]

Can you share the workbook ??
If so, Upload to dropbox or similar and post the link back here

Ok ... Sorry about the delay, but the amendments have been made, and the file can be found here:

Fliename: UserForm ADD Issues

https://www.dropbox.com/s/pvy1wz8b1e...sues.xlsm?dl=0

 

[Michael M]

I've added "New Records" and I've "Updated Records"

ALL with no issues...I can't replicate your problem on my computer
I did however remove the filter first.....and I wonder if that is causing the problem !!!


ALSO...why is there a STOP command halfway through the Add New Record code ???

 

[SGD78]

I've added "New Records" and I've "Updated Records"

ALL with no issues...I can't replicate your problem on my computer
I did however remove the filter first.....and I wonder if that is causing the problem !!!


ALSO...why is there a STOP command halfway through the Add New Record code ???

Hmmm ... That's pretty confusing ... I added the "Stop" for debugging purposes, and I've also tried setting the filter portion as comments to avoid it causing issues.

I'll admit that I'm simply stumped ...

 

[SGD78]

I've added "New Records" and I've "Updated Records"

ALL with no issues...I can't replicate your problem on my computer
I did however remove the filter first.....and I wonder if that is causing the problem !!!


ALSO...why is there a STOP command halfway through the Add New Record code ???

I've tried removing the filter coding and am still getting the error and ultimate crash ...

Frustrating is NOT the word ... Lol

 

[Michael M]

Have you tried it on a different computer ???