I'm still learning how to work with Listboxes and could use some guidance at this point.
I've created a mock-up userform to show records where the date field contains "skipped" so that the user can select specific items/records that he wants to clear the skipped string. The associated Click event for the form button currently clears ALL instances where "skipped" is found. I need to modify so that it will only delete/clear the cells of the items chosen in the Listbox.
clear skips1.jpg
What needs to change in the cmdClearSkips_Click procedure for this to happen?
Private Sub cmdClearSkips_Click()
Dim c As Range
With ActiveSheet
.AutoFilterMode = False
Set c = .UsedRange.Find("SuppDate", , xlValues, xlPart)
If c Is Nothing Then
MsgBox ("Could not find any Skipped accounts.")
Exit Sub
End If
.Range(.Cells(c.Row, c.Column), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, c.Column)).AutoFilter 1, "skipped"
.Range(.Cells(c.Row + 1, c.Column), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, c.Column)).SpecialCells(xlCellTypeVisible).Value = ""
.AutoFilterMode = False
End With
Unload Me
End Sub
Here is the form initialize procedure:
Private Sub UserForm_Initialize()
Dim cell As Range
Dim sAddr As String
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets("Sheet1")
'What:=txtSrchTerm.Text
Set cell = sh.Range("A1:E26").Find( _
What:="skipped", _
After:=Range("D1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
With listSkips
.AddItem cell.Offset(0, -3).Value
.List(.ListCount - 1, 1) = cell.Offset(0, -2).Value
.List(.ListCount - 1, 2) = cell.Offset(0, -1).Value
.List(.ListCount - 1, 3) = cell.Value
.List(.ListCount - 1, 4) = cell.Offset(0, 1).Value
End With
Set cell = sh.Range("A1:E26").FindNext(cell)
Loop While cell.Address <> sAddr
End If
End Sub
I would like some help optimizing this also, but will hold off with those questions until I receive instruction with the first issue.
Attached is a dummy file to work with.
Thank you,
Delain
Bookmarks