VBA userform: 'run-time error 1004' in Excel 2016, but not Excel 2013

[mrspz]

Hi,

So I have the following code

Private Sub CloseForm_Click()Unload Me
End Sub
Private Sub ListBox1_Click()
'Load Textboxes
    For i = 1 To 8
        Controls("TextBox" & i).Value = ListBox1.List(, i - 1)
    Next
End Sub
Private Sub UpdateRow_Click()
'Load sheet with textbox values
Dim i As Long
 If ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
  
    For i = 1 To 8
        Cells(ListBox1.ListIndex + 1, i).Value = Controls("TextBox" & i).Value
    Next
 
'Dim Lastrow As Long
'Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'ListBox1.List = Range("A1:H" & Lastrow).Value
End Sub
Private Sub DeleteRow_Click()
'Delete Row
Dim Lastrow As Long
If ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Rows(ListBox1.ListIndex + 1).EntireRow.Delete
'Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'ListBox1.List = Range("A1:H" & Lastrow).Value
End Sub
Private Sub AddRow_Click()
'Add Row
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
    
    For i = 1 To 8
        Cells(Lastrow, i).Value = Controls("TextBox" & i).Value
    Next
'Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'ListBox1.List = Range("A1:H" & Lastrow).Value
End Sub
Private Sub UserForm1_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
ListBox1.ColumnWidths = "3.25cm"
ListBox1.List = Range("A1:H" & Lastrow).Value
ListBox1.ListIndex = 0
End Sub

as written by a fellow MrExcel user. They (on Excel 2013) were able to run the code fine, however I cannot get it to run properly on Excel 2016.


Clicking the 'UpdateRow' command button works but results in a 'run-time error 1004' error every time, with the code below highlighted in debugging.

        Cells(ListBox1.ListIndex + 1, i).Value = Controls("TextBox" & i).Value

Does anyone have any suggestions?

Original thread

 

[NdNoviceHlp]

Use Option Explicit at the top of your code and be more specific with your intentions.....

Sheets("Sheet1").Cells(ListBox1.ListIndex + 1, i).Value = Userform1.Controls("TextBox" & i).Value

HTH. Dave

 

[NdNoviceHlp]

Whoops... not fully specified yet....

Sheets("Sheet1").Cells(Userform1.ListBox1.ListIndex + 1, i).Value = Userform1.Controls("TextBox" & i).Value

Dave

 

[NdNoviceHlp]

Whoops... not fully specified yet....

Sheets("Sheet1").Cells(Userform1.ListBox1.ListIndex + 1, i).Value = Userform1.Controls("TextBox" & i).Value

Dave
edit: hmmm posted twice
ps. adjust sheet name and userform name to suit

 

[My Aswer Is This]

Just so you know I was the one who wrote the script and it ran perfect using Excel 2013 but would not run perfect using 2016 so are you thinking 2016 requires these type changes?

Whoops... not fully specified yet....

Sheets("Sheet1").Cells(Userform1.ListBox1.ListIndex + 1, i).Value = Userform1.Controls("TextBox" & i).Value

Dave
ps. adjust sheet name and userform name to suit

 

[NdNoviceHlp]

Spurious errors seem like they're often related to lack of variable declaration or lack of code specificity... XL takes it upon itself to run the code the way it wants unless U tell it exactly what to do. I have no idea whether this is the answer to the problem but code that runs on 2013 should run for 2016. I'll note that Jon liked my contribution. Dave

 

[mrspz]

Hmm, that doesn't seem to work.

I'm getting 'run-time error 9, subscript out of range' with the same line of code highlighted as before.

 

[mrspz]

Hmm, that doesn't seem to work.

I'm getting 'run-time error 9, subscript out of range' with the same line of code highlighted as before.

Nope that was my fault - forgot to change the sheet name.

Same 1004 error as before.

 

[My Aswer Is This]

Try putting this script in a New UserForm Command button on your UserForm
and see what happens when you press the button.

Dim i As Long
For i = 1 To 8
Controls("TextBox" & i).Value = i
Next

It should put the numbers 1 to 8 in your UserForm Textboxes we are using for our script we are having problems with.

 

[mrspz]

Yep, that works.