[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Clear selection from list box

I'm trying to embed a loop that will take each selected item from list box 2 and clear those values from a defined range of cells (C4:C50) - first code snippet.


I also included the code that I used to move the selected values from list box 1 to list box 2 - second code snippet.

Any help in finding those cells with a selected value and clearing them is greatly appreciated.  

Thanks!

Dim cell As Range
Dim lItem As Long
Dim wks As Worksheet
Set wks = Sheets("Back End")


For Each cell In wks.Range("C4:C50")
    If cell = UserForm1.ListBox2.Selected(lItem) Then
        cell.ClearContents
    Else
    End If
    Next

Open in new window

Dim lItem As Long
If UserForm1.ComboBox1.Value = "Pricing" Then
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets("Back End").Range("C65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
            ListBox1.Selected(lItem) = False
        End If
    Next
UserForm1.ListBox2.RowSource = ""
UserForm1.ListBox2.RowSource = "Pricing"
Else
End If

Open in new window

0
Golfer219
Asked:
Golfer219
  • 9
  • 6
1 Solution
 
hitsdoshi1Commented:
There you go...

For i = ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(i) = True Then
        ListBox1.RemoveItem i
    End If
Next i
0
 
hitsdoshi1Commented:
I was look at this again and you can just replace your code

ListBox1.Selected(lItem) = False

with


 ListBox1.RemoveItem(lItem)

will do the job...
Dim lItem As Long
If UserForm1.ComboBox1.Value = "Pricing" Then
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets("Back End").Range("C65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
            ListBox1.RemoveItem(lItem)
        End If
    Next
UserForm1.ListBox2.RowSource = ""
UserForm1.ListBox2.RowSource = "Pricing"
Else
End If

Open in new window

0
 
Golfer219Author Commented:
Thanks hitsdoshi1.  Unfortunately that didn't seem to work with what I need.  I'm looking for a loop that will take each selected item from list box 2 and remove it from a specific range (C4:C50) on a given tab.

Thanks!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hitsdoshi1Commented:
if lets say you selected "Item5" from the list box which is in Cell C7, do you want cell C7 to be clear of that value or just delete the row 7?
0
 
Golfer219Author Commented:
Clear it would be great!  The only issue is that "Item5" could be in multiple cells within the given range.  I thought something like what's below would work, but I'm not 100% if this is the best way to do it.  Thanks for your help!

Dim cell As Range
Dim lItem As Long
Dim wks As Worksheet
Set wks = Sheets("Back End")


For Each cell In wks.Range("C4:C50")
    If cell = UserForm1.ListBox2.Selected(lItem) Then
        cell.ClearContents
    Else
    End If
    Next
0
 
hitsdoshi1Commented:
Try this one....if this doesn't work then please attach sample file.

Thanks
For lItem = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(lItem) = True Then
        For Each cell In Sheets("Back End").Range("C4:C50")
            If ActiveCell.Value = ListBox1.Selected(lItem) Then
                ActiveCell.ClearContents
            End If
        Next
    End If
Next

Open in new window

0
 
Golfer219Author Commented:
Thanks!  I tried it but nothing seemed to happen.  I stripped out the portion of the project that we've been discussing and have uploaded it as a sample file.

A couple quick things to note:
 - ComboBox1 drive which ListBox is visible on the right hand side of the user form.  For Pricing, we're focusing on ListBox2.
 - When CommandButton 4 is clicked, the code should run a loop that finds any cell in the given range and clears it's contents so it is removed from the ListBox2 (linked to a named range who's RowSource is updated in the code).

There is a series of other code included - please disregard and focus on Private Sub CommandButton4_Click()


Thanks for all your help! Product-Selector-List-Box-v4.xls
0
 
hitsdoshi1Commented:
so after looking at your file, I see that you are filling list 2 with rowsource method and then trying to use RemoveItem from the list box and then update listbox2 values to Column C.

First problem is getting values in Listbox2. Cos if you want to use RemoveItem, you have to fill the ListBox with AddItem and then only the RemoveItem works. Strange, but really true...(Microsoft Product) :)

Give it a shot and it will work like a charm...

Thanks
0
 
hitsdoshi1Commented:
0
 
Golfer219Author Commented:
Thanks Hitsdoshi1 - I'm defintiely going to keep this in mind in the future.

Based on the current project requirements however (this piece is only a small portion of a much larger reporting solution), we needed to create things a bit different.

Are you aware of any code that can create a loop for each selected item in list box 2 and remove it from the range c4:c50?  If I can get that piece of it, I think everything else should work.

Thanks!

0
 
hitsdoshi1Commented:
There you go...put the following code in your commandbutton 4 sub

It takes the selected value from ListBox 2 and then checks for that value in Column C (C4:C50) and if found then it will clear that value from there.



Private Sub CommandButton4_Click()
Dim lItem As Long
Dim rng As Range
Dim myname As String
Set rng = Sheets("Back End").Range("C4:C50")

If UserForm1.ComboBox1.Value = "Pricing" Then
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox2.Selected(lItem) = True Then
            myname = ListBox2.List(lItem)
            For m = 4 To 50
                If Sheets("Back End").Range("C" & m) = myname Then
                    Sheets("Back End").Range("C" & m).ClearContents
                End If
            Next
        End If
    Next
End If
End Sub

Open in new window

0
 
Golfer219Author Commented:
This worked perfectly!  Thank you SO much for your help and patience -  it was greatly appreciated!
0
 
hitsdoshi1Commented:
Thank you for the points. After looking it again, you might want to change this line:

 For lItem = 0 To ListBox1.ListCount - 1

to refer to ListBox2

Thanks
0
 
Golfer219Author Commented:
Thanks!  Just tested one scenario to see if it would remove multiple selections (after I changed the properties of list box 2 to include multi select) and the code only seems to remove the first selected value.  Did I miss something else?

Thanks!
0
 
hitsdoshi1Commented:
Then I would go with different approach.  Below is the code.

What I did in first part is copy the selected listbox values to cells A151 onwards and then pick up each values and look up in column C and then if found then clear it and go on the next value and repeat the same.

This is work for any number of multi select.


Private Sub CommandButton4_Click()
Dim lItem As Long
Dim myname As String
ctr = 151
Sheets("Back End").Range("A151:A200").ClearContents
If UserForm1.ComboBox1.Value = "Pricing" Then
    For lItem = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(lItem) = True Then
            Sheets("Back End").Range("A" & ctr) = ListBox2.List(lItem)
            ctr = ctr + 1
        End If
    Next
End If

For ctr = 151 To 200
    For ccol = 4 To 50
        If Sheets("Back End").Range("C" & ccol) = Sheets("Back End").Range("A" & ctr) Then
            Sheets("Back End").Range("C" & ccol).ClearContents
        End If
    Next
    If Sheets("Back End").Range("A" & ctr) = "" Then
        Exit For
    End If
Next
End Sub

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now