Remove item from combo box once it's been selected

nokoy

New Member
Joined
Oct 10, 2003
Messages
40
I have a user form that has a combo box that is populated by a range from a worksheet. All the data collected in the user form are then written to a worksheet called Data. What I would like to do is to exclude all the items that has been selected already. Say for example my Rowsource range contains the ff:

Oranges
apples
grapes
bananas
and so on .....

If the user has already selected and entered Oranges(in cell A1) & apples(in cell A2) to the Data worksheet it will no longer be in the dropdown list of the combo box.

Any help will be greatly appreciated.

Thanks

Nokoy
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi Nokoy:

Welcome to MrExcel Board!

You may also want to consider not accepting the value from the UserForm if the selected entry matches with one that had been previously selected (written to the worksheet) -- and prompting the user to select another one. Depending on the project constraints and/or personal preferences, this may not be a wokable alternate in your case.
 
Upvote 0
Yogi,

Thanks for the suggestion but I am trying to eliminate the extra steps when the item is already in the Data worksheet. If there are hundreds of items in the dropdown list and you are down to the last ten, it can be frustrating if you keep on receiving the dialog box telling you to pick another item.


Nokoy
 
Upvote 0
What is the sheet name and range of your source data, and what is the range in the Data sheet that the chosen items end up in?
 
Upvote 0
Tom,

The Worksheet name is "shtSource", the Range name is "rngFruits" and the Combo box name is "cbxFruits". The names does not really matter it can be any name.
I will really appreciate if you can help me with this.

Thanks,

Nokoy
 
Upvote 0
Sorry, when I said "range", I was wondering what the row and column range is, not what the name of the range is. The reason is, there are at least a couple ways to do this. For instance, is your preference to delete the item in the source range (that is, to basically make it shrink) every time a combobox selection is made, or do you want to keep the source range intact, which means you'd be importing all source range items upon userform initialization, then looking at what had been selected before, then deleting those items from the combobox. I'd recommend the first option, but maybe you want to keep the source range all in place for some other reason. Please provide background details on what is happening with your spreadsheet for someone to suggest the best option, which might be one of the aforementioned options or something else altogether that might work better depending on the details you provide.
 
Upvote 0
I do not have any preference, any of your suggestions will work fine with me. Speed will be my first consideration. If there is a big difference in speed between the two, please show me the faster option.

The source range is located in separate worksheet on Colun A. The number or rows can be into hundreds.

There is nothing special with my Excel project so I do not know what kind of details I can tell you.

Thanks,

Nokoy
 
Upvote 0
Here's one way to accomplish what you want, by defining the source range at the time the (I assume) command button is clicked to place the ComboBox selection in the Data sheet. Then, the next time the userform is initialized (assuming further you unload the userform when the ComboBox1 item is transferred to Data), the source range will already have been redefined to only show what has not yet been selected by prior combo boxes.

Remove any range reference in your ComboBox's RowSource property, and place the following code in the userform's module:

Option Explicit

Private Sub CommandButton1_Click()
Dim w As Worksheet, x As Long
Set w = Sheets("shtSource")
x = w.Columns(1).Find(Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues).Row
w.Range("A" & x).EntireRow.Delete
'Rest of your code to transfer ComboBox1 selection to Data sheet goes here
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim w As Worksheet, x As Long
Set w = Sheets("shtSource")
With ComboBox1
x = w.Cells(Rows.Count, 1).End(xlUp).Row
.list = w.Range(w.Cells(1, 1), w.Cells(x, 1)).Value
.ListIndex = 0 'Optional
End With
End Sub
 
Upvote 0
The Following procedures should all be placed in the userform.
These Procedures:
1:will never load any items into cbx that have already been placed on data sheet
2: never go back to source sheet for refresh of data , i.e. the items are removed from cbx list every time item is added to data sheet, without going back to source sheet.
3: Source fruit list is never deleted or modified


Private Sub UserForm_Initialize()
For Each Fruit In Range("rngFruits")
If Len(Trim(Fruit)) > 0 Then
If Not FindFruitInData(Fruit) Then cbxFruits.AddItem Fruit
End If ' not blank
Next Fruit
End Sub

Function FindFruitInData(Fruit) As Boolean
With Worksheets("Data").Cells
Set FruitLoc = .Find(Fruit, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not FruitLoc Is Nothing Then
FindFruitInData = True
Else
FindFruitInData = False
End If
End With
End Function


Private Sub cmdAddToData_Click()
'add fruit to Data Sheet
With Sheets("Data")
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(NxRw, 1).Value = cbxFruits.Value
End With
' Take from combo list
With UserForm1.cbxFruits
.RemoveItem .ListIndex
cbxFruits.Value = ""
End With
End Sub



ASSUMPTIONS:
1: Target Data sheet is called "Data" .. to modify do search for the word "Data" in above procedures
2: On Target sheet fruit are placed in Column 1 ( to modify change the number "1" in (65536, 1). AND NxRw, 1).
3: The source fruit list is found in a user defined range called rngFruits
 
Upvote 0
Thank you so much for all the help. My combo box now works the way I wanted it. Items that were already selected is no longer displayed in the combo box dropdown list saving time of trying to pick or hunt which item is already in the Data sheet.

Again, thanks a lot guys.

Nokoy
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top