Deleting rows Based on data shown in Userform

[Kesosip]

I have a spread sheet that is meant to hell Track Deposits Placed on Beer Kegs and Taps. So far most of the work has been a breeze.

The sheet has a userform for the sales person to enter the information we need such as customers first name, last name, the kegs they purchased, the date, if they rented a tap and how many of the kegs they purchased required a deposit.

so far i have managed to get the form to work for entering new information, and to pull up and display information the problem i’m having is with deleating the info after the keg is returned.

when doing a return the sales person enters the customers last name then clicks a command button that searches for the name then displays all corresponding data in that row. Then i would like to have another command button that uses the same text boxes and deletes the entire row.

thanks for your help

 

[dmt32]

Re: Need Help with Deleating rows Based on data shown in Userform

Hi,
welcome to forum.
Post the code you already have for your userform & this will help others here offering suggestions to assist you.


Dave

 

[Kesosip]

Re: Need Help with Deleating rows Based on data shown in Userform

Hi,
welcome to forum.
Post the code you already have for your userform & this will help others here offering suggestions to assist you.


Dave

Private Sub SearchButton_Click()
row_number = 3
Do
DoEvents
row_number = row_number + 1
    item_in_Review = Sheets("KegMaster").Range("B" & row_number)
        If item_in_Review = LastName2.Text Then
        FirstName2.Text = Sheets("KegMaster").Range("A" & row_number)
        SaleDate2.Text = Sheets("KegMaster").Range("D" & row_number)
        OrderSize.Text = Sheets("KegMaster").Range("E" & row_number)
        TapRental2.Text = Sheets("KegMaster").Range("F" & row_number)
        NumberOfTaps2.Text = Sheets("KegMaster").Range("G" & row_number)
        Deposit2.Text = Sheets("KegMaster").Range("H" & row_number)
        Receipt2.Text = Sheets("KegMaster").Range("I" & row_number).Style = "currency"
        Refund2.Text = Sheets("KegMaster").Range("J" & row_number)
        Kegbox2.Text = Sheets("KegMaster").Range("C" & row_number)
        
   Refund2 = Format(Refund2.Text, "$ #,##0.00")
    End If
    
    Loop Until item_in_Review = ""
    
'end of controls for searching the data base
End Sub

The above code is searches the worksheet and pulls up the info from the row, as for the code to actually Delete I'm at a complete loss Is there anyway to Pass the Row_number to another sub then delete that row?

 

[dmt32]

Re: Need Help with Deleating rows Based on data shown in Userform

Hi,
Untested but see if this update to your code does what you want:

Dim FoundCell As Range
Dim Search As String


Private Sub DeleteButton_Click()
    Dim Response As VbMsgBoxResult
    Dim ctl As Control


    If Not FoundCell Is Nothing Then
'confirm delete action
        Response = MsgBox(Search & Chr(10) & "Do You Want To Delete Record?", 36, "Delete Record")
        If Response = vbYes Then
'delete record
            FoundCell.EntireRow.Delete
'clear variables
            Set FoundCell = Nothing
            Search = ""
'clear record
            For Each ctl In Me.Controls
                If TypeName(ctl) = "TextBox" Then ctl.Text = ""
            Next
'inform user
            MsgBox "Record Deleted", 48, "Record Deleted"
        End If
    End If
End Sub





Private Sub SearchButton_Click()
    Dim Record As Variant
    Dim wsKegMaster As Worksheet
    
'intilaize variables
    Set wsKegMaster = ThisWorkbook.Worksheets("KegMaster")
    Search = Me.LastName2.Text
    
'exit sub if no search value
    If Len(Search) = 0 Then Exit Sub
    
'search record in column B
    Set FoundCell = wsKegMaster.Columns(2).Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
'return record
        With wsKegMaster
            Me.FirstName2.Text = .Range("A" & FoundCell.Row).Text
            Me.SaleDate2.Text = .Range("D" & FoundCell.Row).Text
            Me.OrderSize.Text = .Range("E" & FoundCell.Row).Text
            Me.TapRental2.Text = .Range("F" & FoundCell.Row).Text
            Me.NumberOfTaps2.Text = .Range("G" & FoundCell.Row).Text
            Me.Deposit2.Text = .Range("H" & FoundCell.Row).Text
            Me.Receipt2.Text = .Range("I" & FoundCell.Row).Style = "currency"
            Me.Refund2.Text = .Range("J" & FoundCell.Row).Text
            Me.Kegbox2.Text = .Range("C" & FoundCell.Row).Text
        End With
        Me.Refund2 = Format(Me.Refund2.Text, "$ #,##0.00")
    Else
'inform user
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
    
End Sub

Note the two variables at top of codes. These MUST sit at the very TOP of your Userforms code page OUTSIDE of any procedure.

Dave

 

[Kesosip]

Re: Need Help with Deleating rows Based on data shown in Userform

WOW thank you so Much it worked right away after I corrected a Few of My own typing errors, The only Problem is if Two People have the same last Name it only pulls up the First one, is there an easy way to add the First name to the search? Thanks for help again!

 

[dmt32]

Re: Need Help with Deleating rows Based on data shown in Userform

WOW thank you so Much it worked right away after I corrected a Few of My own typing errors, The only Problem is if Two People have the same last Name it only pulls up the First one, is there an easy way to add the First name to the search? Thanks for help again!

Hi,
glad update to your code helped.

To accommodate additional requirement try following amendments

Dim FoundCell As Range
Dim Search(1 To 2) As String


Private Sub DeleteButton_Click()
    Dim Response As VbMsgBoxResult
    Dim ctl As Control
    If Not FoundCell Is Nothing Then
'confirm delete action
        Response = MsgBox(Search(1) & " " & Search(2) & Chr(10) & "Do You Want To Delete Record?", 36, "Delete Record")
        If Response = vbYes Then
'delete record
            FoundCell.EntireRow.Delete
'clear variables
            Set FoundCell = Nothing
'clear search
            Erase Search
'clear record
            For Each ctl In Me.Controls
                If TypeName(ctl) = "TextBox" Then ctl.Text = ""
            Next
'inform user
            MsgBox "Record Deleted", 48, "Record Deleted"
        End If
    End If
End Sub




Private Sub SearchButton_Click()
    Dim Record As Variant
    Dim FirstAddress As String
    Dim Found As Boolean
    Dim ctl As Control
    Dim wsKegMaster As Worksheet
    
'intilaize variables
    Set wsKegMaster = ThisWorkbook.Worksheets("KegMaster")
    
    Search(1) = Me.FirstName2.Text
    Search(2) = Me.LastName2.Text
    
'exit sub if no search value
    If Len(Search(2)) = 0 Then Exit Sub
    
'search lastname record in column B
    Set FoundCell = wsKegMaster.Columns(2).Find(Search(2), lookat:=xlWhole, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        Found = True
'search for firstname (optional)
        If Len(Search(1)) > 0 Then
            Do
                Found = CBool(FoundCell.Offset(0, -1).Value = Search(1))
                If Found Then Exit Do
                Set FoundCell = wsKegMaster.Columns(2).FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If
    End If
    
    If Found Then
'return record
        With wsKegMaster
            Me.FirstName2.Text = .Range("A" & FoundCell.Row).Text
            Me.SaleDate2.Text = .Range("D" & FoundCell.Row).Text
            Me.OrderSize.Text = .Range("E" & FoundCell.Row).Text
            Me.TapRental2.Text = .Range("F" & FoundCell.Row).Text
            Me.NumberOfTaps2.Text = .Range("G" & FoundCell.Row).Text
            Me.Deposit2.Text = .Range("H" & FoundCell.Row).Text
            Me.Receipt2.Text = .Range("I" & FoundCell.Row).Style = "currency"
            Me.Refund2.Text = .Range("J" & FoundCell.Row).Text
            Me.Kegbox2.Text = .Range("C" & FoundCell.Row).Text
        End With
        Me.Refund2 = Format(Me.Refund2.Text, "$ #,##0.00")
    Else
'inform user
        MsgBox Search(1) & " " & Search(2) & Chr(10) & "Record Not Found", 48, "Not Found"
'clear record
            For Each ctl In Me.Controls
                If TypeName(ctl) = "TextBox" Then ctl.Text = ""
            Next
        Me.LastName2.SetFocus
    End If
    
End Sub

Ensure that you replace all codes as published here.

Updated code is untested but should allow searching with first name included to be optional.

Hope Helpful

Dave