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

SGD78

New Member
Joined
Mar 24, 2016
Messages
31
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)

Rich (BB code):
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hmmm, can't see an orange line OR an underlined one !!
 
Upvote 0
Sorry about that ... got carried away with color coding, and forgot to color the error line.

The error occurs when the Sub tries to add the first cell of data from the form to the table.

Here it is again with the orange line underlined:

Rich (BB code):
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​
Unload Me​
End Sub


Thanks in advance for any useful help with resolving this issue.
 
Last edited:
Upvote 0
Change the line to
Rich (BB code):
Cells(NextRow, 1).Value = Me.cmbx_CustomerName.value
 
Upvote 0
Well, I tried that, and it resulted in an identical critical failure with the same runtime error. :(
 
Upvote 0
I'm assuming you did change both lines ??
Code:
Cells(NextRow, 1).Value = Me.cmbx_CustomerName.value
Cells(NextRow, 2).Value = Me.txtbx_CityState.value

AND

Cells(NextRow, 8).Value = Me.txtbx_AuditDate.Text
Cells(NextRow, 9).Value = Me.cmbx_AuditorsInitials.Text

AND

Cells(NextRow, 11).Value = Me.txtbx_WebAddress.Text

and

nextrow should be declared as Integer
Code:
 Dim NextRow As String  ' should be Integer

The error you are getting generally means the code is going into an infinite loop until failure.
 
Last edited:
Upvote 0
I did in fact change relevant lines from ".Text" to ".Value". I'll try the "String" to "Integer" change and get back to you.

Give me just a second ...
 
Last edited:
Upvote 0
@ JoeMo ... I tried your solution as well, and it gave me the same result ... :(
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top