controlsource cells in listobox

[yoramsk]

hi,
i have created two listboxes in excel such that the choices in the
second listbox are dependant on the what is chosen in the first. for
each listbox i have set the 'controlsource' property to a cell in my
worksheet so that the choices are displayed on the sheet once the
userform is closed. the only problem is that these cells clear as soon
as any new data is entered into the worksheet. these listboxes were
created in vba, not through the add control option...so i don't see any
'linkedcell' property. how can i get the choices made to stay static
after the userform has been closed. appreciate the help. i'm a newbie
so i'm sure it is a simple fix. thank you. btw i'm using excel 2002

 

[Tom Ogilvy]

However, if you have code in your userform
that clears the values in the controls or in this case, changes the
selection in the Listbox, then this could cause this problem.

Since the rowsource is set in code, when you unload the userform, the
rowsource is cleared and would change the selection. Perhaps this is what
is causing your problem.

 

[yoram]

Yes, i see what you're saying and think you are right but do you know
how to prevent this from happening? Anything that I can add to the
code so that the selection is saved even after the userform is unloaded?

 

[Tom Ogilvy]

Instead of using the control source of the listbox, why not use the Click
event to assign the value of the listbox to the cell which was previously
linked. So instead of linking the cell, update the cell with the click
event.

 

[yoram]

ok thanks, i'll try that.

 

[yoram]

i'm trying the following code for your idea:
Private Sub UserForm1_Click()
ListBox1.Value = Worksheets("InternalForm").Range("c12").Value
ListBox2.Value = Worksheets("InternalForm").Range("d12").Value
End Sub

it doesn't seem to work. thanks for the help

 

[yoram]

nevermind...i've got it to work. thanks again for your help.