Why do I get run-time error -2147417848 (80010108) in excel 2013 most of the time I run UserForm?

Question

Task:
I work in Excel2013. I tried to write in VBA a userform to add parameters into dynamic named ranges. All named ranges are held in one sheet and were created using insert>table. I select the range, show existing values and get the new value. All went well untill I actually got to adding value to the range.

Problem:
Excel shuts down most of the time when I try to run the UserForm. Saying:

"Run-time error '-2147417848 (80010108)' Method X of object 'Range' failed"

with different methods ('_Default' last time I checked) at different stages of me breaking code down.

Symtoms:

1. After this line as I found I get the error:

   Cells(y, x) = v
   

   where y and x are integers and v a string I get from the userform. During the debug I checked all values are defined and have values. Moreover, Immediate window with the same numbers input manually (not as variables), works!

2. It mostly doesn't work, though it did follow through doing the job.

If somone could tell the reason why it breaks it would be greatly appreciated!

Some of the captions and potential values are in Unicode in case it matters, though I tried putting it all in English as well.

Private Sub UserForm_Initialize()
    ' Preparing all controls of UserForm
    Sheet2.Activate
    Me.LB_parameter.SetFocus
    Me.LB_parameter.value = ""
    Me.LB_elements.RowSource = ""
    Me.L_element.Enabled = False
    Me.TB_element.Enabled = False
    Me.TB_element.Locked = True
    Me.Btn_Add.Enabled = False
    Me.Btn_Add.Locked = True
End Sub

Private Sub LB_parameter_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Filling the existing list of values for the selected parametr
    If Me.LB_parameter.value <> "" Then
        Me.LB_elements.RowSource = "D_" & Me.LB_parameter.value & "s"
        Me.L_element.Enabled = True
        Me.TB_element.Enabled = True
        Me.TB_element.Locked = False
        Me.TB_element.SetFocus
    End If
End Sub

Private Sub TB_element_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Catching the event of filling out the potential new value
    Me.Btn_Add.Enabled = True
    Me.Btn_Add.Locked = False
    Me.L_element.Enabled = False
    Me.TB_element.Enabled = False
    Me.TB_element.Locked = True
End Sub

Private Sub Btn_Add_Click()
    If Me.TB_element.Text = "" Then
        ' Check if Empty
        MsgBox ("Âû íå âïèñàëè çíà÷åíèå!")
        ' Reset the UserForm
        Me.Btn_Add.Enabled = False
        Me.Btn_Add.Locked = True
        Me.L_element.Enabled = True
        Me.TB_element.Enabled = True
        Me.TB_element.Locked = False
        Me.TB_element.SetFocus
    Else
        ' check if exists
        Dim str
        For Each str In range("D_" & Me.LB_parameter.value & "s")
            If Me.TB_element.Text = str Then
                MsgBox ("Ââåäåííîå çíà÷åíèå óæå ñóùåñòâóåò!")
                ' reset the UserForm
                Me.Btn_Add.Enabled = False
                Me.Btn_Add.Locked = True
                Me.L_element.Enabled = True
                Me.TB_element.Enabled = True
                Me.TB_element.Locked = False
                Me.TB_element.SetFocus
                Me.TB_element.value = ""
                Exit Sub
            End If
        Next str
        ' add to the range here
        Dim x As Integer, y As Integer, v As String
        y = range("D_" & Me.LB_parameter.value & "s").Rows.Count + 2
        x = Me.LB_parameter.ListIndex + 1
        v = Me.TB_element.value
        ' Next line causes break down
        Cells(y, x) = v
        MsgBox ("Âû äîáàâèëè ýëåìåíò:'" & v & "' äëÿ ïàðàìåòðà '" & Me.LB_parameter.value & "'.")
        ' Reset the Userform
        Me.LB_parameter.SetFocus
        Me.LB_parameter.value = ""
        Me.LB_elements.RowSource = ""
        Me.L_element.Enabled = False
        Me.TB_element.Enabled = False
        Me.TB_element.Locked = True
        Me.Btn_Add.Enabled = False
        Me.Btn_Add.Locked = True
    End If
End Sub

Sheet I add values to the parametrs and namedranges window:

The UserForm layout:

Solution 1

Looks like the problem lies in my version of Excel. Not sure if the problem is in my copy or in the 2013 in general. In Excel 2007 on the same machine the UserForm with given suggestions worked continuously without any errors at all! Will update in comments later as I try it in different versions.

Solution 2

Cells(y, x) = v

This call is shorthand for this:

ActiveSheet.Cells(y, x).Value = v

I'm not sure why it's crashing on you, but the _Default property of a Range object being its Value, what I'd try here is being more explicit about what I'm trying to achieve, namely:

• Exactly which Worksheet is supposed to get modified?
• Exactly which Range is being referred to?

I very very very seldom work with ActiveSheet - most of the time I know exactly what object I'm working with. Try using an object. You can create a new one:

Dim target As Worksheet
Set target = ThisWorkbook.Worksheets("pl")

...Or you can give the sheet a code name in the properties toolwindow (F4):

That (Name) property defines an identifier that you can use in VBA code to access a global-scope object that represents that specific worksheet. Assuming that's Sheet1, you could do this:

Sheet1.Cells(x, y) = v

If that still fails, then you can be even more specific about the Range object you're accessing and the property you're setting:

Dim target As Range
Set target = Sheet1.Cells(x, y)
target.Value = v

Normally that wouldn't make a difference though. But I see you're making Range calls, which are also implicitly calling into the ActiveSheet.

I'd start by eliminating these, and working off an explicit object reference.

Then I'd work on getting the spreadsheet logic out of the form; that button click handler is doing way too many things - but I digress into Code Review territory - feel free to post your code there when you get it to work as intended!

Comments

[Daniil Koshelyuk]

Thank you for your advice! Tried rewriting like this: code Dim x As Integer, y As Integer, v As String, targetSheet As Worksheet, targetRange As range y = range("D_" & Me.LB_parameter.value & "s").Rows.Count + 2 x = Me.LB_parameter.ListIndex + 1 v = Me.TB_element.value Set targetSheet = ThisWorkbook.Worksheets("pl") Set targetRange = targetSheet.Cells(y, x) targetRange.value = v code But the problem remained. Looks like it can't access the workbook though it ocaisonally works..

[Daniil Koshelyuk]

also gives error Run-time error '-2147417848 (80010108)' method 'Value' of object 'Range' failed