Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not allow duplicates from listbox to Worksheet
Hi, I have a UserForm that allows multi-selection from a listbox and CommandButton to send the selected items to the worksheet. The UserForm is Modeless and has several CommandButtons to change th rowsource of the listbox. It is now possible to select and enter th same item twice into the sheet, which would create an error in th sheet not easily found. So I need to be able to check that a listbox selection is not duplicate already on the sheet and prevent it from being sent to th sheet. Ideally, any remaining non-duplicate items would be sent to th sheet only the duplicate stopped. My current code has a MsgBox, but I put that in the code as a way t alert me during design. I would love to have this all in th background, without any notification. My current Code generates a 424 run-time error Object Required. I hav run out of ideas how to fix this. Here is the Code: Option Explicit Private Sub cmdEnterSelection_Click() Dim ACol As Long Dim Rng1 As Range Dim rng As Range Dim i As Long Dim j As Long Dim Entries As Long Dim CopyCol As Long Application.ScreenUpdating = False ACol = Sheets("Takeoff").Range("AlphaCol").Column Set Rng1 = Sheets("Takeoff").Range("TakeOffHeaders") Set rng = Sheets("Takeoff").Range("ScopeNames") Entries = Excel.WorksheetFunction.CountA(rng) CopyCol = 1 + Entries For i = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) Then Columns(ACol).Copy Columns(ACol + CopyCol - 1).Select ActiveSheet.Paste If WorksheetFunction.CountIf(Range("ItemDescripTO"), _ ListBox1.List(i, 0).Value) = 0 Then '<<< 424 Error Hit Here With Rng1 .Cells(1, CopyCol).Value = ListBox1.List(i, 0) .Cells(2, CopyCol).Value = ListBox1.List(i, 1) .Cells(4, CopyCol).Value = ListBox1.List(i, 2) .Cells(5, CopyCol).Value = ListBox1.List(i, 3) .Cells(7, CopyCol).Value = ListBox1.List(i, 4) .Cells(8, CopyCol).Value = ListBox1.List(i, 5) .Cells(9, CopyCol).Value = ListBox1.List(i, 6) .Cells(10, CopyCol).Value = ListBox1.List(i, 7) End With Else MsgBox "One of your selections is a" _ & " duplicate." End If CopyCol = CopyCol + 1 End If Next i Entries = Excel.WorksheetFunction.CountA(rng) CopyCol = 1 + Entries Columns(ACol + CopyCol).Clear OptionButton2.Value = True ActiveSheet.Range("E12").Select Application.CutCopyMode = False Application.ScreenUpdating = True End Su -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454 View this thread: http://www.excelforum.com/showthread.php?threadid=54719 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not allow duplicates from listbox to Worksheet
Great VBA Gurus, Please take a look at my post from yesterday. I have searched in the archives until my eyes are crossed and can't find anything new to try. The code I posted yesterday is still the best I can come up with. -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=547199 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet duplicates | Excel Discussion (Misc queries) | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
remove duplicates in listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Getting Duplicates in ListBox | Excel Programming |