Delete a row from a listbox and worksheet simultaneously and bring up row below

[kalumbair]

Hello Everyone,

I could really use some help with my userform andlistbox. I have a userform that containsa listbox with several lines of data. When the user double clicks on a row in the listbox, it populates aseries of textboxes below. The userformalso has all the basic buttons, Delete, Add, Refresh and so on. What I’d like to do is when the user selectsa button it will carry out a particular function.

Right now I’m struggling with the Delete button as I keepgetting an error messages no matter how much I adjust the code. Ultimately, what I want to do is when theDelete button is clicked the data that is in the textboxes will delete from thelistbox and worksheet as well, and bottom rows of data will come up and fill up the listboxand worksheet. The data starts on B3 to goes to J35. If anyone can help outwith this, I’d really appreciate it. Here is the code I have so far, thank you in advance.

Private Sub CommandButton1_Click()
Dim currentrow As Long
Answer = MsgBox("are you sure you want to delete this record?", vbYesNo, "Delete Record")
If Answer = vbYes Then
 Cells(currentrow, 3).EntireRow.Delete
 End If
 End Sub

Private Sub UserForm_Initialize()
 currentrow = 3
 TextBox1 = Cells(currentrow, 1)
 TextBox2 = Cells(currentrow, 2)
 TextBox3 = Cells(currentrow, 3)
 TextBox1 = Cells(currentrow, 4)
 TextBox2 = Cells(currentrow, 6)
 TextBox3 = Cells(currentrow, 7)
 TextBox2 = Cells(currentrow, 8)
 TextBox3 = Cells(currentrow, 9)
 
 End Sub

 

[DanteAmor]

It is important to know how you filled the listbox.
If you filled it with the .Add method or .List property, then you can use .Remove. Then you can delete the sheet row with Delete.


But if you loaded with .RowSource, then you have to delete the record from the sheet. In automatic, the listbox must be loaded or you must use .rowsource again.

 

[kalumbair]

Hi DanteAmor,

Thank you for the speedy reply. The listbox is filed from a table. I tried to attach the file, but I couldn't find the attach icon.

v/
K

 

[kalumbair]

sorry, Dante I miss spoke. the listbox is filled from a rowsource.

 

[DanteAmor]

sorry, Dante I miss spoke. the listbox is filled from a rowsource.

You can put all your code or better upload a file to the cloud.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the file you explain to me how I run the userform.

 

[kalumbair]

Okay Dante,

I uploaded the fileto dropbox and here the shared link. Thefile is pretty simple. You double on arow in the listbox and it populates the textboxes below. I want my code to be able to delete the rowfrom the listbox and the spreadsheet and bring up the bottom row to fill in theempty row both in the listbox and spreadsheet. Is that possible to do?
https://www.dropbox.com/s/yenbsr2yn5z1aan/Customer Tracker.xlsm?dl=0

v/

KS

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim currentrow As Long
  If ListBox1.ListIndex < 1 Then
    MsgBox "Select item from listbox"
    Exit Sub
  End If
  currentrow = ListBox1.ListIndex + 2
  Answer = MsgBox("are you sure you want to delete this record?", vbYesNo, "Delete Record")
  If Answer = vbYes Then
    Rows(currentrow).Delete
    MsgBox "Record deleted"
  End If
End Sub

 

[kalumbair]

Wow,

That worked beautifully. Thank you for the support, brother. Really Appreciate it!!!

v/
K

 

[kalumbair]

Dante,

Upon closer observation, I noticed a problem. The code is working how I imaged. I have 2 problems.

1. It doesn’t actually delete the row I select withdouble click. It deletes the row above the selection andshifts everything up.
   
   
2. When I input new data in, now it doesn’t appearin my listbox.
   

How can I fix that?


v/
K

 

[DanteAmor]

Dante,

Upon closer observation, I noticed a problem. The code is working how I imaged. I have 2 problems.

1. It doesn’t actually delete the row I select with double click. It deletes the row above the selection andshifts everything up.
2. When I input new data in, now it doesn’t appearin my listbox.
   

How can I fix that?

The code sent is for commadbutton, for the double click you must make a new code.
To add data is another code.
I understand that your userform has several functionalities; I am glad to review it; but I suppose you must create a new thread for each problem.

If you want to delete with double click, simply run the code in post #7.