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

kalumbair

New Member
Joined
Aug 2, 2018
Messages
35
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.

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

 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

 
Upvote 0
Try this

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

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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