When Attempting To Add A New Row Of Data From UserForm: runtime error code "-2147417848 (80010108)" method value of object range failed

S

SGD78

New Member
Joined
Mar 24, 2016
Messages
31
Hello all,

I've been designing a new userform to maximize my time-management when doing data entry on a table that I'm maintaining records in. I'm using a recently upgraded version of Excel (when I upgraded to Win10, it came as an automatic update to Microsoft Office 2013).

Everything on my form was working fine until I added a few features to simplify finding records, then my "Add" Command Button stopped working properly and started returning: 'runtime error code: "-2147417848 (80010108)" method "value" of object "range" failed '

I'm fairly new to VBA Coding (I say "new" because it's been well over 13 yrs since I've used excel in any real capacity other than a general spreadsheet, and much has changed since then).

I'm not certain which version of VBA I'm using, but my code is as follows:
(I appologize if the font colors aren't exactly right; I'm color-blind and doing the best that I can, lol. I'm getting an error when the "Sub" attempts to add the first cell of data on the coding line in orange which is also underlined.)

code
Private Sub cmdbtn_Add_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Client Database")
Dim NextRow As Integer
'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
/code

I REALLY need a solution for this issue, so any assistance would be much appreciated.

Thanks in advance.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Ok, well...it's my first time posting in here, so I'm not quite sure what happened when I posted this thread, but NONE of my tabs seem to have taken effect (I'm really glad I chose to color-code the text !!! Lol).

I hope it's still legible enough for someone to help me find a solution though.

Thanks
 
Upvote 0
Well, after a bit of rest and a quick "Tips for Posting" review, I've pinned-down where I made my mistake when posting my question.

So, after a quick typo fix, here it is in the correct format:

Rich (BB code):
Rich (BB code): 
Private Sub cmdbtn_Add_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Client Database")
    Dim NextRow As Integer
    '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
 
Upvote 0
Well, I guess I didn't get quite as much rest as I thought I did, lol.

Here we go again ...

Rich (BB code): 
Private Sub cmdbtn_Add_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Client Database")
    Dim NextRow As Integer
    '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


There we go ... That's a bit better ... :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
149
RoryA
RoryA
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
326
Rhodie72
Rhodie72
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
424
NoSparks
N
N
  • Question Question
Runtime error -2147417848 (80010108) method value of the object range failed
Replies
0
Views
823
nazeefmasood
N

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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