Unanswered: Excel VBA listbox won't accept text value
I have a form with 4 listboxes. Each one is linked to a cell on a spreadsheet, using the control source property. When the form initializes, I want the listboxes to show as selected, the values contained on the linked cells. My understanding of control source property is that it should just work....but what does happen is that the listbox changes the control source cell to contain whatever value was selected when the listbox was saved in design mode. not every time, not every listbox; but often enough that I can't count on the listboxes showing the current values correctly.
I tried using code instead of the control source property. on the listbox_click event, I set the cell equal to the selected value.
workbook.sheets("setup").cells(48,14) = lstEndYear.value
on the form activate event, i tried setting the listbox value equal to the value in the cell:
When I walk through the code step by step, it sets lstEndYear.Text equal to "2002" like I want, but then the listbox click event fires, and about 60% of the time, it resets the lstEndYear.text to "1998" which is the value it had at design time. If I change it at design time to be empty(nothing selected) then it simply changes my value when it runs, to empty.
It's driving me crazy. it just shouldn't be this hard.