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:
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!
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:
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:
Worksheet
is supposed to get modified?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!