VBA userform: modifying/deleting a selected row from a listbox

[mrspz]

Hi there,


I have a listbox which is displaying data from a table in my spreadsheet.


I'd like to be able to select a row and then select a command button to remove this row from the sheet (thus too removing it from the listbox).


In addition, I need to have the data in each collum, of the selected row displayed in textboxes which can be modified. When a textbox has been modified the user can click a command button and the data in the textboxes will be synchronised to the spreadsheet (thus also updating it on the listbox).


If any clarification is required, do let me know - I know this probably reads better myself.


Any help is appreciated! Thanks

 

[My Aswer Is This]

Are you always clicking on some row in the listbox before you click on a button?

Have you modified the code in any way?

 

[mrspz]

Different row everytime.

Only change I've made is adding a '1' after 'userform' in the initialise part since that's the name of my script.

 

[My Aswer Is This]

Try using this new script:

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 UserForm_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

 

[mrspz]

Nope :/

Error 70 with

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

highlighted when adding or deleting.


Error 1004 with

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

highlighted when updating.

 

[My Aswer Is This]

You do have what is know as a Multicolumn listboxbox set to at least 8 columns.

If so and you still have problems I suggest you create a New Userform with just the controls we have discussed.

Do not modify the default names.
And see what happens.

 

[mrspz]

Thanks for the advice.

I need to get some sleep now but I'll try that as soon as I can tomorrow and keep you updated.

 

[mrspz]

So I've created a second userform that only contains the controls we have discussed.

Everything is named as it should be (textbox1-8, UpdateRow, CloseForm, AddRow, DeleteRow). Unfortunately, the same errors still occur as before.

 

[My Aswer Is This]

Is this previous comment of yours still true?
To clarify: yes, the buttons do still serve their function despite resulting in an error afterwards?
What version of Excel are you using?
Are you using a Apple Computer?

Do you have your list Box count set to 8 ?
This would be seen in the properties window for the listbox

Be sure and answer all these questions.

 

[mrspz]

Is this previous comment of yours still true?
Yes - ignoring the errors, everything else functions properly.

What version of Excel are you using?
2016

Are you using a Apple Computer?
No, Windows 10.
Do you have your list Box count set to 8 ?
By this do you mean 'ColumnCount'? If so, yes it is set to 8.

 

[My Aswer Is This]

Try this:
As a test
The listbox will not be updated after each button click. If this fixes the problem we will come up with another solution to do the update.

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 UserForm_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