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

M

mrspz

New Member
Joined
Nov 9, 2017
Messages
37
Hi,

So I have the following code
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.

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

Does anyone have any suggestions?

Original thread
 
Sort by date Sort by votes

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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....
Code: 
Option Base 1
After making these changes the code works fine for me. HTH.Dave
 
Upvote 0
1004 error again with the code below highlighted

Code: 
        Sheets("sheet2").Cells(UserForm1.ListBox1.ListIndex + 1, i).Value = UserForm1.Controls("TextBox" & i).Value
 
Upvote 0
I could have been a bit more clear... the actual error was here...
Code: 
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..
Code: 
ListBox1.ListIndex = 0
The update command button code works without error. Dave
 
Upvote 0
Sorry, I'm a bit confused at what you are trying to say?

I followed your steps and I still have an error.
 
Upvote 0
I was saying that your error wasn't due to the update command button code.

So U have this...
Code: 
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...
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
 
Upvote 0
Yes, I have this

Code: 
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

Code: 
Option Base 1

at the top, yet the error persists.

Here's the entirety of the code:

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
 
Last edited:
Upvote 0
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....
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.
Code: 
UserForm1.Show
It works fine for me. Dave
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
113
Ali M
A
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
266
chazrab
C
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
242
krunal123
K
I
  • Question Question
Run time erorr 94 when selecting value in Listbox
Replies
4
Views
105
ipbr21054
I
C
  • Question Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
533
Edgar_
Edgar_

Forum statistics

Threads
1,224,836
Messages
6,181,248
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top