I have the following code:

Private Sub ClearRepData_Click()

Dim TSLastRep As Integer 'Tally Sheet

For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastRep = ZeroRow - 8

SelectedRep = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
For j = 4 To TSLastRep Step 8
If Cells(j, "A").Value = ListBox1.Selected(i).Value Then
Range("A & j+2: G & j+6, N & j+2: X & j+6").Clear
End If
Next
End If
Next
End Sub

In my second nested If...Then statement I want to compare the value (a name)
in cell Aj with the value (a name) of the selected item from the listbox
object. But Listbox1.Selected(i).Value simply returns a True or False. How
do I show the value of the selected item in the listbox? I realize you can
use ListIndex or Value properties if the ListBox MultiSelect property is set
to 0 but I need my set to 2.

SelectedRep = ""
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
MsgBox .List(i) 'just to show that it works
For j = 4 To TSLastRep Step 8
If Cells(j, "A").Value = .List(i) Then
Cells(j + 2, "A").Resize(5, 7).ClearContents
Cells(j + 2, "N").Resize(5, 11).ClearContents
End If
Next j
End If
Next i
End With

I changed the .clear to .clearcontents, too. (Along with the syntax for that
line.)



Bishop wrote:

I have the following code:

Private Sub ClearRepData_Click()

Dim TSLastRep As Integer 'Tally Sheet

For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastRep = ZeroRow - 8

SelectedRep = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
For j = 4 To TSLastRep Step 8
If Cells(j, "A").Value = ListBox1.Selected(i).Value Then
Range("A & j+2: G & j+6, N & j+2: X & j+6").Clear
End If
Next
End If
Next
End Sub

In my second nested If...Then statement I want to compare the value (a name)
in cell Aj with the value (a name) of the selected item from the listbox
object. But Listbox1.Selected(i).Value simply returns a True or False. How
do I show the value of the selected item in the listbox? I realize you can
use ListIndex or Value properties if the ListBox MultiSelect property is set
to 0 but I need my set to 2.

--

Dave Peterson

ps. You may want to ignore the upper/lower case of the names:

If Cells(j, "A").Value = .List(i) Then
becomes
If lcase(Cells(j, "A").Value) = lcase(.List(i)) Then



Dave Peterson wrote:

SelectedRep = ""
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
MsgBox .List(i) 'just to show that it works
For j = 4 To TSLastRep Step 8
If Cells(j, "A").Value = .List(i) Then
Cells(j + 2, "A").Resize(5, 7).ClearContents
Cells(j + 2, "N").Resize(5, 11).ClearContents
End If
Next j
End If
Next i
End With

I changed the .clear to .clearcontents, too. (Along with the syntax for that
line.)

Bishop wrote:

I have the following code:

Private Sub ClearRepData_Click()

Dim TSLastRep As Integer 'Tally Sheet

For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastRep = ZeroRow - 8

SelectedRep = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
For j = 4 To TSLastRep Step 8
If Cells(j, "A").Value = ListBox1.Selected(i).Value Then
Range("A & j+2: G & j+6, N & j+2: X & j+6").Clear
End If
Next
End If
Next
End Sub

In my second nested If...Then statement I want to compare the value (a name)
in cell Aj with the value (a name) of the selected item from the listbox
object. But Listbox1.Selected(i).Value simply returns a True or False. How
do I show the value of the selected item in the listbox? I realize you can
use ListIndex or Value properties if the ListBox MultiSelect property is set
to 0 but I need my set to 2.

--

Dave Peterson

--

Dave Peterson