ListBox Duplicating selection

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,814
Office Version
  1. 365
Platform
  1. Windows
I have a ListBox that puts the selected items, from the List, onto a worksheet/tab

When the user selects an item from the list (Multiple selection allowed) it puts the value on the tab but it also repeats the previous selections

Lets say the ListBox has a list of 1 through 10 in it

If the user selects #1, 1 appears in A1 on "SelectedCats"
If the user then selects #5, in A2 #1 is loaded and #5 is loaded in A3
If the user then select #8, #1 loads in A4, #5 loads in A5 and #8 loads in A6 and so on

I dont want it to keep loading the previous selections , I juts want to see #1, #5 and #8

Here is the Code
Private Sub ListBox1_Change()
Sheets("SelectedCats").Visible = True
UnProtectit
Dim Sh As Worksheet
Dim Rng As Range
Dim NextCell As Range
Dim i As Long
Dim r As Long
Dim BoundCol As Long

Set Sh = Worksheets("SelectedCats")

With Sh
If Not IsEmpty(.Cells(1, 1)) Then
Set Rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set NextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
End With

With ListBox1
BoundCol = .BoundColumn
' Add new selections
For i = 1 To .ListCount - 1
If .Selected(i) Then
If Not Rng Is Nothing Then
On Error Resume Next
r = WorksheetFunction.Match(.List(i, BoundCol), Rng, False)
If Err <> 0 Then
Err.Clear
NextCell = .List(i, BoundCol)
End If
On Error GoTo 0
Else
Sh.Cells(1, 1) = .List(i, BoundCol)
End If
With Sh
Set Rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set NextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

End If
Next i

End With
Protectit
Sheets("SelectedCats").Visible = True
End Sub
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,123,115
Messages
5,599,800
Members
414,340
Latest member
HEzim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top