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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
183
Edgar_
Edgar_
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
461
pure vito
P
C
  • Question
Porting an Excel application to Access
Replies
6
Views
252
chazrab
C
T
  • Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
202
tubrak
T
D
  • Solved
Search value in textbox1 in the listbox1 with array doesn't work
Replies
4
Views
168
david2005wang
D

Forum statistics

Threads
1,215,429
Messages
6,124,835
Members
449,192
Latest member
mcgeeaudrey

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