Code for submit button to delete row dependant on combobox value

**mezza89** · 04-02-2014, 05:52 PM

Hi there have uploaded this spreadsheet a few times now. I am on my final question now.

I have created userform3 and started to program it however some suggestions on finishing it please!

I want an item to be selected in combobox1. Click delete item and a warning 'Are you sure you want to delete item'. Cancel = close form or yes to search for the row in sheet 1 and sheet 2 and delete the entire row.

Look forward to your help.

**mezza89** · 04-03-2014, 04:59 AM

Any ideas anyone?

**jaslake** · 04-03-2014, 12:54 PM

Hi mezza89

Try this

Private Sub CommandButton1_Click()
  Dim lrow As Long, lReply As Long

  lrow = Range("PRODUCTS").Find(Me.ComboBox1.Value).Row

  lReply = MsgBox(Prompt:="Are you sure you want to delete item " & Me.ComboBox1.Value, _
                  Buttons:=vbYesNoCancel, Title:="Delete Item")

  If lReply = vbCancel Then
    Exit Sub
  ElseIf lReply = vbNo Then
    Exit Sub
  End If

  With Sheet1.Columns(1)
    .Rows(lrow).EntireRow.Delete
  End With

  With Sheet2
    lrow = .Columns("A").Find(what:=Me.ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole).Row
    .Rows(lrow).EntireRow.Delete
  End With

  ComboBox1.Value = ""

  Unload Me

End Sub

**mezza89** · 04-04-2014, 06:32 AM

Hi thanks for your help works perfectly how ever is it possible you can help with one more bit of code?

At the moment if the product is not in sheet2 it comes up with debugger obviously because the object is not there. Can we tweak the code so that it will ignore sheet 2 if the product is not there?

Thanks in advance :-)

**jaslake** · 04-04-2014, 10:01 AM

Hi mezza89

Try this

Private Sub CommandButton1_Click()
  Dim lrow As Long, lReply As Long

  lrow = Range("PRODUCTS").Find(Me.ComboBox1.Value).Row

  lReply = MsgBox(Prompt:="Are you sure you want to delete item " & Me.ComboBox1.Value, _
                  Buttons:=vbYesNoCancel, Title:="Delete Item")

  If lReply = vbCancel Then
    Exit Sub
  ElseIf lReply = vbNo Then
    Exit Sub
  End If

  With Sheet1.Columns(1)
    .Rows(lrow).EntireRow.Delete
  End With

  With Sheet2
    On Error GoTo SkipMe
    lrow = .Columns("A").Find(what:=Me.ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole).Row
    .Rows(lrow).EntireRow.Delete
  End With

SkipMe:
  ComboBox1.Value = ""
  Unload Me
End Sub

Code for submit button to delete row dependant on combobox value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Code for submit button to delete row dependant on combobox value

Re: Code for submit button to delete row dependant on combobox value

Re: Code for submit button to delete row dependant on combobox value

Re: Code for submit button to delete row dependant on combobox value

Re: Code for submit button to delete row dependant on combobox value

Thread Information

Users Browsing this Thread

Similar Threads

Macro delete submit button when item selected from drop dowm list

[SOLVED] Enforcing submit button in userform with combobox?

Custom Form with Submit Button, Email and then Submit to Database

Code to reset combobox dependant on another combobox

Create a Submit button save filename as A1, submit email unc path, etc.

Bookmarks

Bookmarks

Posting Permissions