CODEWITHSUNDEEP
vbaexcel
BezukhovPierre
BezukhovPierre

Reputation: 11

Remove value from range populating my listbox

Hello everyone I have two listboxes. The first listbox contains all the items to choose from. After selecting an item, a user clicks on an 'ADD' command button to copy that value onto the range of the second listbox. I believe most of you have seen similar add/remove listboxes.

Both listboxes were created by inserting controls and they are populated by an input range of items on a hidden worksheet.

Here is my problem: adding names is works fine, however the 'remove' procedure I created seems to take a long time to complete since the list of items can be more than 200 items.

I use the following code to match a selected listbox value with the input range value and then it clears the contents of the cell in the input range: 

Sub remove()


Dim r As Long
Dim al As ListBox

Dim d As Range
Dim dd As Range
Dim allpick As Worksheet


Set al = Worksheets("LISTBOX").ListBoxes("listselected")
Set allpick = Worksheets("columns")
Set dd = allpick.Range("selectedNAMES")


With al
    For r = 1 To .ListCount
        If .selected(r) Then
            For Each d In dd
                If d.Value = .List(r) Then
                d.ClearContents
                End If
            Next d
        End If

    Next r
End With

End Sub

Is there an alternative code or structure I could use so that it doesn't take so long to complete?

Upvotes: 0

Views: 180

Answers (1)

BezukhovPierre
BezukhovPierre

Reputation: 11

I used the find function stated by commenters and wrote the code below. It is much faster and is exactly what I wanted. However, I didn't know what to put after "IF CELL IS NOTHING THEN" so i just used calculate. Any suggestions?

Dim r As Long
Dim al As ListBox
Dim strNAME As String
Dim names As Worksheet
Set names = Worksheets("names")
Set al = Worksheets("HOME").ListBoxes("selectednames")


With al
    For r = 1 To .ListCount
        If .Selected(r) Then
        strNAME = .List(r)
        Set cell = names.Range("currentnames").Find(What:=strNAME)

            If cell Is Nothing Then
                Calculate

            Else: cell.ClearContents
            End If
        End If
    Next r
End With

Upvotes: 0

Related Questions