Results 1 to 8 of 8

Clear the Skips (Listbox selections)

Threaded View

  1. #1
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Clear the Skips (Listbox selections)

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cascading Dropdowns that clear old selections
    By TAB9296 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-29-2015, 11:54 AM
  2. Clear ( 6 ) Option Button selections in ( 1 ) Group Box
    By Koddy in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-22-2015, 03:12 AM
  3. [SOLVED] Listbox Selections Not Taking
    By wwalkerbout in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-11-2013, 03:54 PM
  4. Populating 1 listbox on the basis of another listbox selections
    By protocol in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2011, 07:33 AM
  5. List box selections clear during Save As
    By Matt W in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2011, 04:55 PM
  6. ListBox with multiple selections
    By Tony Bender in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2009, 04:01 PM
  7. Multiple selections from a listbox
    By yzer19 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2007, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1