Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Userform; cmbbox uses named range source, changes unexpecte
I'm building a workbook to collect data from several dozen people. The
workbook will be centrally located on a server, so the intent was to make it easy to collect the data without having a complicated worksheet. I built a userform that starts with a combobox at the top for the user to select their name. Private Sub cmbNameSelection_Change() triggers some code that sets a group of subsequent combobox values equal to some spreadsheet cell values that are associated with that user on a hidden worksheet. Some may be populated, some may be blank. I also have a commandbutton to allow users to add items to the list (named range) behind the comboboxes. The commandbutton triggers an inputbox, then pushes the new value to the list and re-sorts the list to retain alphabetical order. There is a submit button at the bottom of the userform; when the user has completed making entries, this button writes the current combobox values back to their worksheet range. The problem: When testing, I put in a value for a data entry combobox, then add a new value to the list so I can add it in a subsequent combobox. When I add the value to the list, the value in my first combobox changes. For example, I pre-populate the list (dynamic named range) with AAA, CCC, DDD, and EEE. I select DDD in the first userform combobox, and AAA in the second. I then use the cmdbutton to add BBB to the list. The combobox set at AAA is fine, but the DDD combobox has changed to CCC. It appears that the combobox is retaining an index number of some kind, and when the underlying list is updated, the new item with that index number is shown, instead of retaining the current value. I guess one option is that I can grab all the values (26 total) prior to the new value being added, then reset all the combobox values based on the text strings... but I would really have expected the combobox to retain the value set by the user. Is this expected behavior, and are there any more eloquent workarounds? Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Userform; cmbbox uses named range source, changesunexpecte
On May 1, 7:49*pm, ker_01 wrote:
I'm building a workbook to collect data from several dozen people. The workbook will be centrally located on a server, so the intent was to make it easy to collect the data without having a complicated worksheet. I built a userform that starts with a combobox at the top for the user to select their name. Private Sub cmbNameSelection_Change() triggers some code that sets a group of subsequent combobox values equal to some spreadsheet cell values that are associated with that user on a hidden worksheet. Some may be populated, some may be blank. I also have a commandbutton to allow users to add items to the list (named range) behind the comboboxes. The commandbutton triggers an inputbox, then pushes the new value to the list and re-sorts the list to retain alphabetical order. There is a submit button at the bottom of the userform; when the user has completed making entries, this button writes the current combobox values back to their worksheet range. The problem: When testing, I put in a value for a data entry combobox, then add a new value to the list so I can add it in a subsequent combobox. When I add the value to the list, the value in my first combobox changes. For example, I pre-populate the list (dynamic named range) with AAA, CCC, DDD, and EEE. I select DDD in the first userform combobox, and AAA in the second. I then use the cmdbutton to add BBB to the list. The combobox set at AAA is fine, but the DDD combobox has changed to CCC. It appears that the combobox is retaining an index number of some kind, and when the underlying list is updated, the new item with that index number is shown, instead of retaining the current value. I guess one option is that I can grab all the values (26 total) prior to the new value being added, then reset all the combobox values based on the text strings... but I would really have expected the combobox to retain the value set by the user. Is this expected behavior, and are there any more eloquent workarounds? Thank you, Keith Keith, Do you have some code to post from your cmdbutton_Click and combobox_Change events? You may have some syntax that is changing the .ListIndex of your combo box. Otherwise, you could store the .ListIndex value prior to adding items and then set the .ListIndex to the stored valued after the items have been added. Best, Matthew Herbert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a named range as a data source for a chart | Excel Worksheet Functions | |||
Named Range as Chart Source | Excel Programming | |||
Using a named range as a source for a combobox on a userform | Excel Programming | |||
Use named range to fill Userform combobox XL2003? | Excel Programming | |||
Combobox and named range as source | Excel Programming |