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]

Option Explicit
Option Base 1
Dim Lastrow As Long

Private Sub UserForm_Activate()
'loads userform, selects 1st row of data
Lastrow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
UserForm1.ListBox1.ColumnCount = 7
UserForm1.ListBox1.ColumnWidths = "3.25cm"
UserForm1.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value
UserForm1.ListBox1.ListIndex = 0
End Sub

Private Sub ListBox1_Click()
Dim i As Integer
'Loads textboxes with selected listbox values
For i = 1 To 7
UserForm1.Controls("TextBox" & i).Value = UserForm1.ListBox1.List(, i - 1)
Next i
End Sub

Private Sub CommandButton1_Click()
'loads textboxes with 1-7 values????
Dim i As Long
For i = 1 To 7
UserForm1.Controls("TextBox" & i).Value = i
Next i
UserForm1.ListBox1.ListIndex = -1
End Sub

Private Sub UpdateRow_Click()
'Loads sheet2 with textbox values
'Sheet2 row update based on listbox selection
'updates listbox. Selects updated row
Dim i As Long, Temp As Integer
If UserForm1.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Lastrow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
For i = 1 To 7
Sheets("sheet2").Cells(UserForm1.ListBox1.ListIndex + 1, i).Value = _
       UserForm1.Controls("TextBox" & i).Value
Next i
Temp = UserForm1.ListBox1.ListIndex
UserForm1.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value
UserForm1.ListBox1.ListIndex = Temp
End Sub

Private Sub DeleteRow_Click()
'Delete sheet2 data based on listbox selection
'updates listbox. Selects same listbox location that was removed
Dim Temp As Integer
If UserForm1.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Temp = UserForm1.ListBox1.ListIndex
Sheets("sheet2").Rows(UserForm1.ListBox1.ListIndex + 1).EntireRow.Delete
Lastrow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
UserForm1.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value
'listbox selection
If Temp + 1 = Lastrow Then
UserForm1.ListBox1.ListIndex = Temp - 1
Else
UserForm1.ListBox1.ListIndex = Temp
End If
End Sub

Private Sub AddRow_Click()
'Adds Row bottom of data on sheet2
'updates listbox and selects new addition
Dim i As Long
Lastrow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 7
Sheets("sheet2").Cells(Lastrow + 1, i).Value = UserForm1.Controls("TextBox" & i).Value
Next i
UserForm1.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow + 1).Value
UserForm1.ListBox1.ListIndex = Lastrow
End Sub

I don't get/understand your error? Dave

 

[mrspz]

Hi,

So the first error is that I can only update the first column, since modifying a textbox other than the first, followed by selecting the update button results in an error.

The second issue is that an error occurs when using this with data that contains decimal numbers (this includes dates, times and currency).

Hope that helps to clarify the situation.

 

[NdNoviceHlp]

The code posted was thoroughly tested and works without error for any data type in any textbox. The only thing I can suggest again, is to take the 5-10 minutes it takes to trial a new wb. Start a new wb, add a "Sheet2", add a "userform1", add a "listbox1" to the userfor, add x7 textboxes (textbox1,textbox2, etc.) to the userform, add x4 command buttons to the userform named "Commandbutton1", "UpdateRow", "DeleteRow" and "AddRow".... Note: that this is a Name change not a Caption change. Place the above code in the userform code. This will be my final contribution. Good luck. Dave