Conditional Row Deleting
 

On UserForm3 I have TextBox1, TextBox2, and TextBox3, and
CommandButton1 named "Delete".

On Sheet2 I have a table of data in Range A2:C52.
In this table, ColumnA contains Extensions, ColumnB
contains Employee Names, and ColumnB contains Email
Addresses.

On the UserForm3, TextBox1 looks in ColumnA, TextBox2
looks in ColumnB, and TextBox3 looks in ColumnC.

What I need is for when the user clicks the "Delete"
button on the UserForm, I need the code look on Sheet2
Range A2:C52 to see if there is a match. In this case, a
match would mean the value of TextBox1 matches a value of
a cell in ColumnA in the table, the Value of TextBox2
matches a value of a cell in ColumnB in the corresponding
row, and the value of TextBox3 matches a value of a cell
in ColumnC of the corresponding row. If all the values in
the textBoxes(textBox 1, 2 and 3) match the values of a
cell in Columns A, B, and C in the same row, then I need
for that row in the table to be deleted. If the values of
all 3 Textboxes do not match values of cells in the same
row, then I need for the code not to run and have a
message saying "Not Valid" pop up to the user.

What is the code to make this happen?

Thank you
Todd Huttenstine

Conditional Row Deleting
 

I don't understand why you chose to use that setup, and require the user to enter three perfect matches in order to delete. It's very tiring for the user

How about use a multi-column listbox to display your data, and after the user has select an item in the list, and press the delete button, the row in the spreadsheet is deleted

1) Insert a ListBox onto your userform. Call it ListBox1
2) Select the ListBox1 and in Properties window, set the "ColumnCount" to 3
3) You got a Delete button called CommandButton1
4) Place the following code into your userfor

'--------------------------------------------
Private Sub CommandButton1_Click(
With ListBox
If .ListCount 0 The
Worksheets(2).Rows(.ListIndex + 2).Delet
.RemoveItem .ListInde
End I
End Wit
End Su

Private Sub UserForm_Activate(
With Worksheets(2).Range("A1").CurrentRegio
If .Rows.Count 1 The
ListBox1.List() = .Offset(1, 0).Resize(.Rows.Count - 1, 3).Valu
ListBox1.ListIndex =
End I
End Wit
End Su
'---------------------------------------------

Conditional Row Deleting
 

I really dont want to do that because the textboxes all
auto-populate based on the value of a combobox, therefor
the user will not have to type anything into the textboxes.



-----Original Message-----
I don't understand why you chose to use that setup, and
require the user to enter three perfect matches in order
to delete. It's very tiring for the user.

How about use a multi-column listbox to display your
data, and after the user has select an item in the list,
and press the delete button, the row in the spreadsheet is
deleted?

1) Insert a ListBox onto your userform. Call it ListBox1.
2) Select the ListBox1 and in Properties window, set
the "ColumnCount" to 3.
3) You got a Delete button called CommandButton1.
4) Place the following code into your userform

'---------------------------------------------
Private Sub CommandButton1_Click()
With ListBox1
If .ListCount 0 Then
Worksheets(2).Rows(.ListIndex + 2).Delete
.RemoveItem .ListIndex
End If
End With
End Sub

Private Sub UserForm_Activate()
With Worksheets(2).Range("A1").CurrentRegion
If .Rows.Count 1 Then
ListBox1.List() = .Offset(1, 0).Resize
(.Rows.Count - 1, 3).Value
ListBox1.ListIndex = 0
End If
End With
End Sub
'---------------------------------------------
.

Conditional Row Deleting
 

OK. Assume your delete button is called "CommandButton1". Your TextBoxes are called "TextBox1","TextBox2" and "TextBox3".

Place the following code into your commandbutton's click event.

'---------------------------------------------------
Private Sub CommandButton1_Click()
Dim c
Dim firstAddress As String, tmp As Integer
Dim tmp_array()

With Worksheets(2)
Set c = .Range("A1").CurrentRegion.Columns(1).Find(TextBox 1.Text, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1).Value = TextBox2.Text And c.Offset(0, 2).Value = TextBox3.Text Then
On Error GoTo err1
ReDim Preserve tmp_array(UBound(tmp_array) + 1)
On Error GoTo 0
tmp_array(UBound(tmp_array)) = c.Address(False, False)
End If
Set c = .Range("A1").CurrentRegion.Columns(1).FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
On Error GoTo err2
For tmp = UBound(tmp_array) To 1 Step -1
.Range(tmp_array(tmp)).EntireRow.Delete
Next
End If
End With
err2:
Exit Sub

err1:
ReDim tmp_array(1)
Resume Next
End Sub
'---------------------------------------------------

----- Todd Huttenstine wrote: -----

I really dont want to do that because the textboxes all
auto-populate based on the value of a combobox, therefor
the user will not have to type anything into the textboxes.



-----Original Message-----
I don't understand why you chose to use that setup, and
require the user to enter three perfect matches in order
to delete. It's very tiring for the user.
How about use a multi-column listbox to display your
data, and after the user has select an item in the list,
and press the delete button, the row in the spreadsheet is
deleted?
1) Insert a ListBox onto your userform. Call it ListBox1.
2) Select the ListBox1 and in Properties window, set
the "ColumnCount" to 3.
3) You got a Delete button called CommandButton1.
4) Place the following code into your userform
'---------------------------------------------
Private Sub CommandButton1_Click()
With ListBox1
If .ListCount 0 Then
Worksheets(2).Rows(.ListIndex + 2).Delete
.RemoveItem .ListIndex
End If
End With
End Sub
Private Sub UserForm_Activate()
With Worksheets(2).Range("A1").CurrentRegion
If .Rows.Count 1 Then
ListBox1.List() = .Offset(1, 0).Resize
(.Rows.Count - 1, 3).Value
ListBox1.ListIndex = 0
End If
End With
End Sub
'---------------------------------------------
.

Conditional Row Deleting
 

Thank you.

That worked great.


-----Original Message-----
OK. Assume your delete button is called "CommandButton1".
Your TextBoxes are called "TextBox1","TextBox2"
and "TextBox3".

Place the following code into your commandbutton's click
event.

'---------------------------------------------------
Private Sub CommandButton1_Click()
Dim c
Dim firstAddress As String, tmp As Integer
Dim tmp_array()

With Worksheets(2)
Set c = .Range("A1").CurrentRegion.Columns(1).Find
(TextBox1.Text, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1).Value = TextBox2.Text And
c.Offset(0, 2).Value = TextBox3.Text Then
On Error GoTo err1
ReDim Preserve tmp_array(UBound
(tmp_array) + 1)
On Error GoTo 0
tmp_array(UBound(tmp_array)) = c.Address
(False, False)
End If
Set c = .Range("A1").CurrentRegion.Columns
(1).FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
On Error GoTo err2
For tmp = UBound(tmp_array) To 1 Step -1
.Range(tmp_array(tmp)).EntireRow.Delete
Next
End If
End With
err2:
Exit Sub

err1:
ReDim tmp_array(1)
Resume Next
End Sub
'---------------------------------------------------

----- Todd Huttenstine wrote: -----

I really dont want to do that because the textboxes
all
auto-populate based on the value of a combobox,
therefor
the user will not have to type anything into the
textboxes.



-----Original Message-----
I don't understand why you chose to use that setup,
and
require the user to enter three perfect matches in
order
to delete. It's very tiring for the user.
How about use a multi-column listbox to display
your
data, and after the user has select an item in the
list,
and press the delete button, the row in the
spreadsheet is
deleted?
1) Insert a ListBox onto your userform. Call it
ListBox1.
2) Select the ListBox1 and in Properties window,
set
the "ColumnCount" to 3.
3) You got a Delete button called CommandButton1.
4) Place the following code into your userform
'---------------------------------------------
Private Sub CommandButton1_Click()
With ListBox1
If .ListCount 0 Then
Worksheets(2).Rows(.ListIndex + 2).Delete
.RemoveItem .ListIndex
End If
End With
End Sub
Private Sub UserForm_Activate()
With Worksheets(2).Range("A1").CurrentRegion
If .Rows.Count 1 Then
ListBox1.List() = .Offset(1, 0).Resize
(.Rows.Count - 1, 3).Value
ListBox1.ListIndex = 0
End If
End With
End Sub
'---------------------------------------------
.

.