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]

Have you tried it on a different computer ???

Indeed I have, and same results ...

 

[Michael M]

I'm only stabbing the dark here, but......is it ALL add records or only sometimes with add records..eg, if you DONT / DO omit some data, or put erroneous data into one of the boxes ??

 

[SGD78]

I'm only stabbing the dark here, but......is it ALL add records or only sometimes with add records..eg, if you DONT / DO omit some data, or put erroneous data into one of the boxes ??

I only get the error and crash when using the ADD button ... Everything else works just as intended ...

 

[SGD78]

I only get the error and crash when using the ADD button ... Everything else works just as intended ...

That is to say that even with the filter code removed (or disabled) the Add button is the only piece of code that is crashing the system (regardless of whether or not I've left any (or all) fields of data on the userform blank).

 

[Michael M]

Ok...If it helps, I've narrowed it down to a version issue !!
It runs fine on 2007 or earlier....but CRASHES in 2013 or higher !!!

I'm still playing with it, but did this get created in an earlier version of Excel ??

 

[Michael M]

Also, have a read of this post...in particular post #17...the last one to do with rowsource properties in ComboBoxes


http://www.mrexcel.com/forum/excel-questions/890481-method-value-object-range-failed-2.html

 

[Michael M]

So, from reading the other post I'm guessing that when trying to ADD to a fixed table range, it fails...in which case it would have to be the Records_List, as you aren't adding anything to the Auditors List.
Change the Records_list back to a normal range and the issue should be resolved.

 

[SGD78]

So, from reading the other post I'm guessing that when trying to ADD to a fixed table range, it fails...in which case it would have to be the Records_List, as you aren't adding anything to the Auditors List.
Change the Records_list back to a normal range and the issue should be resolved.

So, I am in fact using a 2013 or later version of Excel. I've implemented the changes you've suggested, and EVERYTHING WORKS BEAUTIFULLY !!!

Thank you SOOOOO very much Michael M. !!! I deeply appreciate your relentless assistance in resolving this highly frustrating issue and your finding of this innocuously small (yet highly effective) fix !!!

Many Thanks !!!

 

[Michael M]

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

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

 

[Michael M]

Ok, got it

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

I disabled all this when I was trying to solve the problem, and as I was only using the first few rows of the combobox, didn't need to scroll....seniors moment...