delete entry on worksheet via userform listbox

**confused325** · 09-21-2009, 11:36 AM

Hi there,

I've nearly finished my workbook, very exciting it's the first time i've ever done this kind of thing- I feel like a computer programmer : )

Anyway I've got stuck on the last bit and thought someone here might be able to help.

Basically I have a userform to show the entries on a sheet that is constantly being added to. So far I have a textbox that you can enter a search term in and it brings up all matches in a listbox.
I tried copying from other help forums bits of code but I can't make it work. The latest I tried is

Code:

'For single-select listbox
With Listbox1
If .ListIndex <> -1 Then
Range(.ListboxRange).Rows(.ListIndex + 1).EntireRow.Delete
End If
End With
End Sub

I think I change the listbox range to ProductRange but i have no idea what listindex is?

That's the most important part but if you have more time and it's easy could you help me with a second thing.
Underneath the listbox I have textboxes for each column so when you highlight a line in the listbox the textboxes all fill up. I want to allow users to make changes in there and then press a save button to overwrite the line. Again looking elsewhere I gather this is quite complicated but if you can help...

Many thanks and hope you're having a good day

A confused birthday girl : )

**confused325** · 09-21-2009, 01:19 PM

Sorry, I'm really desperate and this is the headache i didn't want on my bday

I've got slightly further but I don't understand whether I'm meant to put a Dim statement (which incidently I'm guessing at) for each of my column headers. And what is NumDel on about?
This is what I have and underneath is the original.

Code:

Private Sub cmdDelete_Click()
   
Dim NumDel
Dim r As Range
Dim BADel As Long
Dim NameDel As String
Dim faddress As String
Dim TotalDel As Double
Dim RateDel As Double
Dim SCDel As Double
    With Me
    
        CCDel = .Listbox1.Column(0)
        BlankDel = .Listbox1.Column(1)
        NumDel = .Listbox1.Column(2)
        TotalDel = Listbox1.Column(3)
        DojDel = Listbox1.Column(4)
        ATDel = Listbox1.Column(5)
        RateDel = Listbox1.Column(6)
        TCDel = Listbox1.Column(7)
        SCDel = Listbox1.Column(8)
        JobdescDel = Listbox1.Column(9)
        ActDel = Listbox1.Column(10)
        WkDel = Listbox1.Column(11)
        PNDel = Listbox1.Column(12)
        FacDel = Listbox1.Column(13)
        MCDel = Listbox1.Column(14)
        PCDel = Listbox1.Column(15)
        StockDel = Listbox1.Column(16)
        BTDel = Listbox1.Column(17)
    End With
    
    NumDel = Application.WorksheetFunction.Substitute(NumDel, "-", "")
    NumDel = NumDel * 1
    
    With Sheets("DATABASE").Range("ProductRange")
        Set r = .Find(What:=WBSDel, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
        
        If Not r Is Nothing Then
            faddress = r.Address
            Do
                frow = r.Row
                If Cells(frow, 2).Value = NumDel And _
                    Cells(frow, 3) = BADel And _
                        Cells(frow, 4) = PriceDel Then
                    Cells(frow, 1).EntireRow.Delete
                End If
                Set r = .Find(What:=NameDel, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
            Loop While Not r Is Nothing
        Else
            MsgBox "This Product Has Not Been Found, Please Try Again", vbInformation + vbOKOnly, "Product Not Found"
            Me.txtSearch = ""
            Me.txtSearch.SetFocus
        End If
        
    End With
    
    With Me
        .txtWBS.Value = Null
        .txtTotal.Value = Null
        .txtDoj.Value = Null
        .txtAT.Value = Null
        .txtRate.Value = Null
        .txtSC.Value = Null
        .txtJobdesc.Value = Null
        .txtAct.Value = Null
        .txtWk.Value = Null
        .txtPN.Value = Null
        .txtFac.Value = Null
        .txtBT.Value = Null
        .txtPC.Value = Null
        .txtStock.Value = Null
        .txtMC.Value = Null
        .txtTC.Value = Null
        
        
        .Listbox1.Column(0) = ""
        .Listbox1.Column(1) = ""
        .Listbox1.Column(2) = ""
        .Listbox1.Column(3) = ""
        .Listbox1.Column(4) = ""
        .Listbox1.Column(5) = ""
        .Listbox1.Column(6) = ""
        .Listbox1.Column(7) = ""
        .Listbox1.Column(8) = ""
        .Listbox1.Column(9) = ""
        .Listbox1.Column(10) = ""
        .Listbox1.Column(11) = ""
        .Listbox1.Column(12) = ""
        .Listbox1.Column(13) = ""
        .Listbox1.Column(14) = ""
        .Listbox1.Column(15) = ""
        .Listbox1.Column(16) = ""
        .Listbox1.Column(17) = ""
        .Listbox1.Column(18) = ""
        .Listbox1.Column(19) = ""
    End With
        
End Sub

Original:

Code:

Private Sub cmdDelete_Click()
    
Dim NumDel
Dim r As Range
Dim BADel As Long
Dim Del As String
Dim faddress As String
Dim TotalDel As Double

    With Me
        NameDel = .Listbox1.Column(0)
        NumDel = .Listbox1.Column(1)
        BADel = .Listbox1.Column(2)
        PriceDel = .Listbox1.Column(3)
    End With
    
    NumDel = Application.WorksheetFunction.Substitute(NumDel, "-", "")
    NumDel = NumDel * 1
    
    With Sheets("Products").Range("ProductRange")
        Set r = .Find(What:=NameDel, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
        
        If Not r Is Nothing Then
            faddress = r.Address
            Do
                frow = r.Row
                If Cells(frow, 2).Value = NumDel And _
                    Cells(frow, 3) = BADel And _
                        Cells(frow, 4) = PriceDel Then
                    Cells(frow, 1).EntireRow.Delete
                End If
                Set r = .Find(What:=NameDel, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
            Loop While Not r Is Nothing
        Else
            MsgBox "This Product Has Not Been Found, Please Try Again", vbInformation + vbOKOnly, "Product Not Found"
            Me.txtSearch = ""
            Me.txtSearch.SetFocus
        End If
        
    End With
    
    With Me
        .txtSearch.Value = Null
        .txtBA.Value = Null
        .txtDescription.Value = Null
        .txtPrice.Value = Null
        .txtProductNumber.Value = Null
        .Listbox1.Column(0) = ""
        .Listbox1.Column(1) = ""
        .Listbox1.Column(2) = ""
        .Listbox1.Column(3) = ""
    End With
        
End Sub

Thread: delete entry on worksheet via userform listbox

Thread Tools

Display

delete entry on worksheet via userform listbox

Re: delete entry on worksheet via userform listbox

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions