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]

You said:
I have a listbox which is displaying data from a table in my spreadsheet

What data is the listbox displaying?

What is the name of the listbox?


You said:
I'd like to be able to select a row
How do you plan to select the row.

When you say remove the row do you mean delete the row?


You said:

In addition, I need to have the data in each collum, of the selected row displayed in textboxes which can be modified.
So if you have 40 columns of data you will need 40 textboxes

You said:
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).

Do you mean the data will be entered into the first empty row in the active sheet?

We need more details here.

 

[mrspz]

Sorry, let me try to clarify some things.

Below is an example of how the userform could look

Since I have selected the fourth row, this is the data that is being displayed in the textboxes below.

* If I were to edit one of these textboxes and select 'update row' the changes would be applied to the spreadsheet (pictured below) thus updating them in the listbox

* If I select 'delete row' the row is removed from the spreadsheet and thus removed from the listbox as well

* If I select 'add new row' the data that is currently displayed in the textboxes is added to the spreadsheet in a new row (the first empty box)

Sorry - hope this clears everything up.

 

[My Aswer Is This]

Show me all the code you already have.

You said you have already written code to load the listbox
And are we only going to be loading data into seven textboxes
And just be aware of not getting to far along in naming all your textboxes.
Users some times want to set up things like this and do not take into consideration the coding needed to load these boxes. Like they should keep there default names like TextBox1 and TextBox2

How many columns of data is in this Multicolumn listbox"

 

[My Aswer Is This]

Since I have not heard from you lets try this:
Assuming here the following
We want to work with columns A to G
And will load all rows from Row(1) to Lastrow into listbox
I have given this script these names
ListBox is Named "ListBox1"
We have Four Command Buttons named
All these command buttons must be named exactly as show or you will have to Modify script.
AddRow
DeleteRow
UpdateRow
CloseForm
You will also see a Initilize script
This will load your listbox with your activesheet rows
It also sets the listbox columns width to "3.25cm"
You can change this if you want.
Let me know if this works like you want.

Private Sub CloseForm_Click()
Unload Me
End Sub
Private Sub ListBox1_Click()
'Load Textboxes
    For i = 1 To 7
        Controls("TextBox" & i).Value = ListBox1.List(, i - 1)
    Next
End Sub
Private Sub UpdateRow_Click()
'Load sheet with textbox values
Dim i As Long
  
    For i = 1 To 7
        Cells(ListBox1.ListIndex + 1, i).Value = Controls("TextBox" & i).Value
    Next
 
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:G" & Lastrow)
ListBox1.List = Data
End Sub
Private Sub DeleteRow_Click()
'Delete Row
Rows(ListBox1.ListIndex + 1).EntireRow.Delete
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:G" & Lastrow)
ListBox1.List = Data
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
    
    For i = 1 To 7
        Cells(Lastrow, i).Value = Controls("TextBox" & i).Value
    Next
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:G" & Lastrow)
ListBox1.List = Data
End Sub
Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:G" & Lastrow)
ListBox1.ColumnWidths = "3.25cm"
ListBox1.List = Data
ListBox1.ListIndex = 0
End Sub

 

[My Aswer Is This]

O yes I forgot.

We have 7 textbox's on the Userform
They are named:

TextBox1
TextBox2
TextBox3

etc.

 

[mrspz]

Sorry for the lack of the reply - I'll get back to this as soon as I can. I suspect we are in very different timezones.



Thanks in advance.

 

[mrspz]

Hi,

Sorry for the late response. This works almost perfectly.

Two problems at the moment:

1. The information in the textbox isn't formatted properly (with the exceptionof TB4 oddly enough)

2. There are in fact 8 columns, not 7. This is entirely my fault - apologises.
3. While the buttons do work as intended, they all result in a 'Run-time error 70' pop up immediately after

Again, thank you. I really appreciate the time you have spent on this.

 

[My Aswer Is This]

OK try this:
This takes into consideration We are working with Range("A1:H" & lastrow)
This means column A Row 1 to column H and goes downs as far as there is data in Column "A"
It looks for the last row in column A with data

Be sure and name your new textbox TextBox8

We will work on formatting TextBoxes after we get this working with no errors
What type data are you putting into these cells where the formatting is wrong?
Are they dates?

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
  
    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
Data = Range("A1:H" & Lastrow)
ListBox1.List = Data
End Sub
Private Sub DeleteRow_Click()
'Delete Row
Rows(ListBox1.ListIndex + 1).EntireRow.Delete
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:H" & Lastrow)
ListBox1.List = Data
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
    
    For i = 1 To 8
        Cells(Lastrow, i).Value = Controls("TextBox" & i).Value
    Next
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:H" & Lastrow)
ListBox1.List = Data
End Sub
Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Data = Range("A1:H" & Lastrow)
ListBox1.ColumnWidths = "3.25cm"
ListBox1.List = Data
ListBox1.ListIndex = 0
End Sub

 

[mrspz]

New column working, errors persist. Could it be because the sheet it needs to apply these changes to is called 'sheet2'?

edit: columns 2-6 are times (hh:mm).