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

 

[mrspz]

Hi,

Sorry I did not mean to mess you round. I thought I'd made the data types clear when I originally posted with this image:

* A: Date (DD/MM/YYYY)
* B-F: 24 hour time (HH:MM)
* G: Currency (£x.xx)/(£x,xxx)/(£x,xxx.xx)

 

[mrspz]

Just seven columns... I know. My testing sheet contains eight for reasons I can't figure out, that's clearly what was confusing me before too.

Seven is the correct amount though.

 

[My Aswer Is This]

Maybe later I can look back at this:
It's not easy when you keep changing things.
First your image seemed to show seven columns then you said sorry it should be 8 so I changed it to 8 now your saying it's 7

 

[mrspz]

I know - I'm sorry to fustrate. I'd been working on a separate test sheet and hadn't noticed I had the columns arranged incorrectly.

 

[My Aswer Is This]

I assume if you change the number of columns then you know how to change the script.
Their would be several places where you would need to change the script like for 1 to 8 or 1 to 7
And the range would change from A to G or A to H which ever it might be

I did a quick test and it did not matter what type value were in these ranges I got no errors
Are the values a result of formulas in the cells.

And it's impossible for me to even know what script your using because there have been several others here telling you to make changes to your script which I really did not think were needed but hey try if you want.

 

[mrspz]

I assume if you change the number of columns then you know how to change the script.
Their would be several places where you would need to change the script like for 1 to 8 or 1 to 7
And the range would change from A to G or A to H which ever it might be

That's fine, I can work that out.

Are the values a result of formulas in the cells.

Yes, I hadn't even considered that this would be an issue.

And it's impossible for me to even know what script your using because there have been several others here telling you to make changes to your script which I really did not think were needed but hey try if you want.

Yeah, it hasn't changed too much though.

Option ExplicitOption Base 1


Private Sub CloseForm_Click()
Unload Me
End Sub


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


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


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

 

[NdNoviceHlp]

U said it was now "G" instead of "H"? U need to change these parts of the code....

For i = 1 To 8

to....

For i = 1 To 7

and

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:H" & Lastrow).Value

to....

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value

As I said before, I had given some time to trying your code on XL 16 for U. It works as posted for "H" and 8 textboxes. Now U have only seven columns of data for 8 textboxes? I'm assuming that this just posted to the forum wrong...

Option ExplicitOption Base 1

should look like this...

Option Explicit
Option Base 1

I think U will find that your add/delete rows code doesn't do what U want but I'm not too certain what that is. Good luck with this. Dave

 

[NdNoviceHlp]

U said it was now "G" instead of "H"? U need to change these parts of the code....

For i = 1 To 8

to....

For i = 1 To 7

and

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:H" & Lastrow).Value

to....

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value

As I said before, I had given some time to trying your code on XL 16 for U. It works as posted for "H" and 8 textboxes. Now U have only seven rows of data for 8 textboxes? I'm assuming that this just posted to the forum wrong...

Option ExplicitOption Base 1

should look like this...

Option Explicit
Option Base 1

I think U will find that your add/delete rows code doesn't do what U want but I'm not too certain what that is. Good luck with this. Dave

 

[My Aswer Is This]

NdNovice
I'm going to jump out of this thread. Since your using 2016 and the user is also using 2016.
The script I wrote worked perfect for me as I understood his needs.

And it seems as if you think my entire script was written wrong and that may be so. I would think it would be best if you wrote the user a entire new script. You should be able to see and understand what he wants.
Thanks for helping out here.

U said it was now "G" instead of "H"? U need to change these parts of the code....

For i = 1 To 8

to....

For i = 1 To 7

and

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:H" & Lastrow).Value

to....

UserForm2.ListBox1.List = Sheets("sheet2").Range("A1:G" & Lastrow).Value

As I said before, I had given some time to trying your code on XL 16 for U. It works as posted for "H" and 8 textboxes. Now U have only seven rows of data for 8 textboxes? I'm assuming that this just posted to the forum wrong...

Option ExplicitOption Base 1

should look like this...

Option Explicit
Option Base 1

I think U will find that your add/delete rows code doesn't do what U want but I'm not too certain what that is. Good luck with this. Dave

 

[mrspz]

Hi, sorry to be late to get back to you. I've done what you described.

So currently:

* Updating only works for the first column/textbox, all the others just revert back to their original numbers.

* Updating data that contains a decimal number (e.g. a date, time or currency) results in a 1004 error