K
ker_01
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
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