doble click to modify selected record in listbox and right click to delete

[BLUEPOMME]

hello

i like to have help with vba codes :

i have a listbox in userform4 i like to add a code to use right click to modify record using userform1 (userfrom1 is used to add new data to list)
and to do right click on mouse to delete selected record from list

please see file and original post
http://www.excelforum.com/excel-pro...ord-in-listbox-and-right-click-to-delete.html

thank you

 

[DanteAmor]

Of all the columns there is some unique data to use as a key?

 

[BLUEPOMME]

just column b,c and d fills automatically with formulas once the other colums are filled in , the last column fills automatically with vba ones you fill date in first column but there will be always a diferent data en each row

 

[DanteAmor]

The key field is used to identify a record as unique. That is, there are no two identical data in the column. For your comments, you don't have a key field, so I'm going to use the temp sheet to load the data into the listbox.

 

[BLUEPOMME]

if it is very complicated i don´t like to make you trubble or to much work on it

what i have right now managed with code is to fill the userfrom1 with the information i need from the listbox , please can ou only help me to complete the follow code to delete the entire row from table of the selected record from listbox.

Private Sub LB_00_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


  With Me.LB_00
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
    
        UserForm1.TextBox2.Value = .List(i, 5)
        UserForm1.ComboBox2.Value = .List(i, 4)
        UserForm1.TextBox4.Value = .List(i, 6)
        UserForm1.ComboBox3.Value = .List(i, 7)
        UserForm1.ComboBox5.Value = .List(i, 9)
        UserForm1.TextBox5.Value = .List(i, 10)
        UserForm1.ComboBox1.Value = Format(.List(i, 8), "hh:mm")
        UserForm1.TextBox6.Value = Format(.List(i, 0), "dd/mm/yy")
        
        UserForm1.Show

* here need the code to delete entire row from table of the selected record in listbox  *

and would it be posible to add a button (need code) on userform4 to delete selected record on listbox and table

thank you for all your help and effort you doing this for me

 

[BLUEPOMME]

if it is very complicated i don´t like to make you trubble or to much work on it

what i have right now managed with code is to fill the userfrom1 with the information i need from the listbox , please can ou only help me to complete the follow code to delete the entire row from table of the selected record from listbox.

Private Sub LB_00_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


  With Me.LB_00
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
    
        UserForm1.TextBox2.Value = .List(i, 5)
        UserForm1.ComboBox2.Value = .List(i, 4)
        UserForm1.TextBox4.Value = .List(i, 6)
        UserForm1.ComboBox3.Value = .List(i, 7)
        UserForm1.ComboBox5.Value = .List(i, 9)
        UserForm1.TextBox5.Value = .List(i, 10)
        UserForm1.ComboBox1.Value = Format(.List(i, 8), "hh:mm")
        UserForm1.TextBox6.Value = Format(.List(i, 0), "dd/mm/yy")
        
        UserForm1.Show

* here need the code to delete entire row from table of the selected record in listbox  *

and would it be posible to add a button (need code) on userform4 to delete selected record on listbox and table

thank you for all your help and effort you doing this for me

(SORRY I TRY TO EXPLAIN BETTER IN THIS POST )

if it is very complicated i don´t like to make you trubble or to much work on it ....

what i have right now managed so far with code is to fill in the userfrom1 with the information i need from the listbox making doble click ,
since y add the selected record as a new one once i modifyed it in userfrom1.
i only need your help with to finish the poseted code to delete the selected record from table (the one i selected with doble click)

Private Sub LB_00_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


  With Me.LB_00
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
    
        UserForm1.TextBox2.Value = .List(i, 5)
        UserForm1.ComboBox2.Value = .List(i, 4)
        UserForm1.TextBox4.Value = .List(i, 6)
        UserForm1.ComboBox3.Value = .List(i, 7)
        UserForm1.ComboBox5.Value = .List(i, 9)
        UserForm1.TextBox5.Value = .List(i, 10)
        UserForm1.ComboBox1.Value = Format(.List(i, 8), "hh:mm")
        UserForm1.TextBox6.Value = Format(.List(i, 0), "dd/mm/yy")
        
        UserForm1.Show

* here need the code to delete entire row from table of the selected record in listbox  *

also would it be posible to show me the code for the delete buttom to delete selected record on listbox and table.

thank you for all your help and effort you doing this for me

 

[DanteAmor]

* here need the code to delete entire row from table of the selected record in listb

If you have thousands of records, which one will be deleted? for that a key field is required, if you don't have it, then I must create it. It's a complicated thing, but it can be done. Don't worry, I've done it before. I give it to you on Monday, because now it's Sunday and I'm with the family.

 

[BLUEPOMME]

i dont like to hurry neither it is urgent i appreciate your help , enjoy your day off with family !!! thank you

 

[DanteAmor]

Try this.

Your listbox has a very strange behavior. I suggest you start in a new book, new sheets and copy the forms to the new book.

Dim sh As Worksheet, sht As Worksheet


Private Sub CommandButton1_Click()
  If LB_00.ListIndex = -1 Then
    MsgBox "Select item"
    Exit Sub
  End If
  If LB_00.Selected(LB_00.ListIndex) = False Then
    MsgBox "Select item"
    Exit Sub
  End If
  '
  'borrar registro
  Dim fila As Long
  'recupera el número de fila almacenado en el listbox
  fila = Val(LB_00.List(LB_00.ListIndex, 12))
  sh.Rows(fila).Delete
  Call Load_ListBox
  LB_00.Height = "177.95"
End Sub


Private Sub LB_00_Click()
  LB_00.Height = "177.95"
End Sub


Private Sub UserForm_activate()
  Dim r As Range, j As Long
  Application.ScreenUpdating = False
  Set sh = Sheets("EVENTOS")
  Set sht = Sheets("Temp")
  C_00.List = Split("FECHA LUGAR HABITACION NOMBRE ESTADO VENDEDOR")
  With LB_00
    .ColumnCount = 13
    .ColumnHeads = True
    .ColumnWidths = ("100;100;100;0;80;50;250;100;0;100;100;200;0")
  End With
  Call Load_ListBox
End Sub


Sub Load_ListBox()
  Dim i As Long, j As Long
  i = 2
  j = 2
  sht.Cells.Clear
  sh.Rows(1).Copy
  sht.Range("A1").PasteSpecial xlPasteValues
  Do While sh.Range("A" & i) <> ""
    If T_00 = "" Then
      sh.Rows(i).Copy
      sht.Range("A" & j).PasteSpecial xlPasteValues
      sht.Range("A" & j).PasteSpecial xlPasteFormats
      sht.Range("M" & j).Value = i  'almacena el número de fila
      j = j + 1
    Else
      'aplicar filtros
          
    End If
    i = i + 1
  Loop
  LB_00.RowSource = sht.Name & "!A2:M" & j - 1
  Application.CutCopyMode = False
End Sub

The wbook:

https://www.dropbox.com/s/r0kitajjhq7og9h/agenda cenas romanticas1.xlsm?dl=0

 

[BLUEPOMME]

thank you i posted the code as you indicated

and is working smooth ...

only i notice is when i filter column in C_00 to search with T_00 it does nothing , is there some code to be compleate on the T_00 change event ?

also how i can use the delete code after i finish the follow

Private Sub LB_00_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


  
  With Me.LB_00
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
      
      
    
        UserForm1.TextBox2.Value = .List(i, 5)
        UserForm1.ComboBox2.Value = .List(i, 4)
        UserForm1.TextBox4.Value = .List(i, 6)
        UserForm1.ComboBox3.Value = .List(i, 7)
        UserForm1.ComboBox5.Value = .List(i, 9)
        UserForm1.TextBox5.Value = .List(i, 10)
        UserForm1.ComboBox1.Value = Format(.List(i, 8), "hh:mm")
        UserForm1.TextBox6.Value = Format(.List(i, 0), "dd/mm/yy")
        
        UserForm1.Show

*add code to delete old record after userfrom1 close*

sorry to bother you again

 

[DanteAmor]

thank you i posted the code as you indicated

and is working smooth ...

only i notice is when i filter column in C_00 to search with T_00 it does nothing , is there some code to be compleate on the T_00 change event ?

also how i can use the delete code after i finish the follow I don't understand what you mean by this part.

sorry to bother you again

The code to search is not enabled, it must be created again.


You must also create a code to select a record and modify it (this is even more complicated, since you are handling 2 userform, one to search and another to modify - I guess -)

As I told you, you have several things in mind, but we are going step by step, I help you with everything, but ask for one thing at a time, if you ask several things this becomes chaos.