:-( dont know how to do this VBA

[mahmed1]

Hi,

i have a userform listbox which polulates data from a my worksheet

now here is what i want to do on the userform

i want to have a delete and edit command buttons

from the listbox..if i select a record and press delete then i want to look in my sheet and delete that record and edit the record on the sheet based on what amendments i make on the userform (i have text boxes to put my amendments in) but the main thing is i cant figure out how to delete or modify based on record selected in the userform

 

[Norie]

It depends on how you are populating the listbox and what range you are populating it from.

It could be very simple, for example if you've populated from a contiguous range then the ListIndex of the selecte item will correspond to the row it comes from in the range.

So with that you could do the delete/amend on the appropriate row.

 

[mahmed1]

Hi Norie,

can you please show me how this could be done. I am copying from a contiguos range from the worksheet

thank you

 

[mahmed1]

Any Luck Norie

Thank You

 

[Norie]

ADVERTISEMENT

How can you populate a listbox by copying from a range?

 

[mahmed1]

Hi Norie

i did it like this

listbox.rowsource=sheets("sheet1").range("a1:c100").address(0,0)

 

[Norie]

Replace that with this.

listbox.List=Sheets("sheet1").Range("A1:C100").Value

You can then use this to delete the selected item.

Private Sub cmdDelete_Click()
Dim idx As Long

       idx = listbox.ListIndex


       If idx = -1 Then
           MsgBox "No item selected."
       Else
           Sheets("Sheet1").Range("A" & idx+1 & ":C" & idx+1).Delete Shift:=xlShiftUp
       End If

End Sub

To update the values for the selected item.

Private Sub cmdUpdate_Click()
Dim rng As Range
Dim idx As Long

       idx = listbox.ListIndex

       If idx = -1 Then
           MsgBox "No item selected."
       Else
           Set rng = Sheets("Sheet1").Range("A" & idx + 1)

           rng.Value = Textbox1.Value
           rng.Offset(,1) = Textbox2.Value
           rng.Offset(,2) = Textbox3.Value
       End If

End Sub

You'll probably need to adjust the code to suit your own setup, but that's the general idea of how to delete and amend based on a listbox.

PS If you have a header in row 1 then you'll need to use idx + 2 instead of idx + 1.

 

[mahmed1]

Thank you