Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box Unique Entries
All, I have 5 active x combo boxes on a worksheet. All have the same listfillrange property. Is there a way that once an item is selected in one combo box that particular item cannot be selected in the other combo boxes (or does not even appear in the other combo boxes). thanks much for your help. RK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box Unique Entries
A way - probably, but it's at least modestly painful. These comments are without having ever done this or any testing, so take them with a rather large grain of salt. Having said that, check if a combobox's listfillrange can be specified as a named range. If it can, you can move the full list of choices into a named range (FullList), another named range (CurrentList), and have other named ranges set up for each of the comboboxes (ComboList_1 through ComboList_5). All named ranges start out as copies of FullList. Then, when an arbitrary item in an arbitrary combobox is selected, write code in its _Click event to remove the selected item from CurrentList and to set the named ranges for the other comboboxes(but not the one where the selection was just made or any ComboBox that already has a select!) to be the same as the updated CurrentList. Of course, if the use de-selects an item, you would have to add it back to CurrentList and update all comboboxes that don't have a current selection. Things would be easier if you could count on the user first making a selection in ComboBox1, then in ComboBox2, etc - but you know how users are. You could actually enforce that by having all the ComboBoxes disabled, except for the first, and using the _Click event of ComboBox1 to enable ComboBox2 and so on. You would still have to dynamically update the named range associated with the next ComboBox to be selected. This breaks down, though, once all ComboBoxes have been selected - then the user can change any one, and you're back to the complexity of having to manage all of the named ranges any time any ComboBox selection is changed. Unless, of course, at the same time you enable ComboBox2 because a selection has been made in ComboBox1 you disable ComboBox1. Extending this logic means that there is always one and only one ComboBox for which a selection can be made, but now you have to have an item in the dynamic named ranges that, when selected, disables the current ComboBox and enables the previous one. Most complex! I wouldn't start this late at night without a LOT of whatever caffeine delivery system you prefer. All in all, you might take a look at OptionButtons and see if they can do what you want - might be a lot simpler. Good luck on this :Bgr and be sure to post back on how it went/what worked if you do tackle it! -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109671 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box Unique Entries
Never underestimate the amount of work that has already been done in Excel!!! Check out 'Excel -- Data Validation -- Hide Previous Selections' (http://www.contextures.com/xlDataVal03.html) for a way to do what you want with Data Validation - looks a LOT simpler than anything I suggested! -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109671 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box Unique Entries
i did answer a similar question a week or two ago and linked a demo
spreadsheet. the idea was that the listfill range was a range named table and when an item was selected, the code attached to the combobo (1) copied the item to a cell immediately to the left of the combobox and (2) deleted the item from the table. -- though it was data validation rather than comboboxes, the logic is similar. "rk0909" wrote in message ... All, I have 5 active x combo boxes on a worksheet. All have the same listfillrange property. Is there a way that once an item is selected in one combo box that particular item cannot be selected in the other combo boxes (or does not even appear in the other combo boxes). thanks much for your help. RK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum unique entries only | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Worksheet Functions | |||
Unique Entries | Excel Programming |