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

 

[My Aswer Is This]

That proves all the textboxes are named correctly.

Yep, that works.

 

[NdNoviceHlp]

OK I set up a trial wb and userform in XL 2016. The resolution is rather simple even though it never hurts to clearly define your variables and specify your intentions. Change your userform Initialize code to userform Activate code and place this at the top of your code....

Option Base 1

After making these changes the code works fine for me. HTH.Dave

 

[mrspz]

1004 error again with the code below highlighted

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

 

[NdNoviceHlp]

I could have been a bit more clear... the actual error was here...

Private Sub ListBox1_Click()
'Load Textboxes
    For i = 1 To 8
        Controls("TextBox" & i).Value = ListBox1.List(, i - 1)
    Next
End Sub

This part of the code in your original initialize code was generating the error..

ListBox1.ListIndex = 0

The update command button code works without error. Dave

 

[mrspz]

Sorry, I'm a bit confused at what you are trying to say?

I followed your steps and I still have an error.

 

[NdNoviceHlp]

I was saying that your error wasn't due to the update command button code.

So U have this...

Private Sub UserForm_Activate()
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

along with this at the top of your code...

Option Base 1

You have already established that your textboxes are named correctly. Do U have a Sheet2? Is your userform named Userform1? Is your listbox named Listbox1? Your code works fine for me in 2016 after I made the changes as outlined. It should work for U. Dave

 

[mrspz]

Yes, I have this

Private Sub UserForm1_Activate()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

Along with this

Option Base 1

at the top, yet the error persists.

Here's the entirety of the code:

Option Base 1
Private Sub CloseForm_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To 8
Controls("TextBox" & i).Value = i
Next
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
        Sheets("sheet2").Cells(UserForm1.ListBox1.ListIndex + 1, i).Value = UserForm1.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_Activate()
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

 

[NdNoviceHlp]

I have a wb with "sheet2" and data A1:H8; a userform (Userform1); x4 command buttons on the userform named commandbutton1, UpdateRow, DeleteRow, and AddRow; a listbox (named Listbox1); and 8 textboxes named Textbox1, Textbox2. etc. Here's the entire code....

Option Explicit
Option Base 1

Private Sub CloseForm_Click()
Unload Me
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To 8
UserForm1.Controls("TextBox" & i).Value = i
Next
End Sub

Private Sub ListBox1_Click()
Dim i As Integer
'Load Textboxes
    For i = 1 To 8
       UserForm1.Controls("TextBox" & i).Value = UserForm1.ListBox1.List(, i - 1)
    Next
End Sub

Private Sub UpdateRow_Click()
'Load sheet with textbox values
Dim i As Long
 If UserForm1.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
  
    For i = 1 To 8
        Sheets("sheet2").Cells(UserForm1.ListBox1.ListIndex + 1, i).Value = UserForm1.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 UserForm1.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Sheets("sheet2").Rows(UserForm1.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 = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
If UserForm1.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
    
    For i = 1 To 8
        Sheets("sheet2").Cells(Lastrow, i).Value = UserForm1.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 UserForm_Activate()
Dim Lastrow As Long
Lastrow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
UserForm1.ListBox1.ColumnWidths = "3.25cm"
UserForm1.ListBox1.List = Sheets("sheet2").Range("A1:H" & Lastrow).Value
UserForm1.ListBox1.ListIndex = 0
End Sub

If this doesn't work, I suggest U set up a new wb. Add sheet2, enter data A1:H8, add a userform, add x4 command buttons and change the names, add a listbox, and then add x8 textboxes. Insert the code in the userform code.

UserForm1.Show

It works fine for me. Dave

 

[mrspz]

Hi,

So I've done some further investigating and managed to find the root of this error.

It looks like the error was being caused by the way the cells were formatted; as dates and times. This is problematic as with my intended use I need to be able to display this type of data.

When I display the data as numbers (either whole or decimal) the code is able to run without errors. Although, I encountered a new bug though while testing this: I'm only able to update the first column/textbox. Changing the value of any textbox with the exception of TextBox1 and clicking the update button simply results in the original value being reverted back.

 

[My Aswer Is This]

Well glad to see you have part of this sorted out. From the beginning I do not believe you pointed out what sort of data you were dealing with. I think now not looking back to be sure your dealing with dates and times and maybe other such data.
So hopefully you can sort this out.
I believe Excel looks at all data entered into textboxes as text.

And my loop running through all those textboxes would have to be modified to deal with this.

Maybe you could point out exactly what type data will be going in and out of each textbox