Deleting rows Based on data shown in Userform

Kesosip

New Member
Joined
Dec 7, 2017
Messages
3
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2017
Messages
3
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

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
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:


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

New Member
Joined
Dec 7, 2017
Messages
3
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,086
Members
414,501
Latest member
mdhaumyu

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
Top