controlsource cells in listobox
 

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

controlsource cells in listobox
 

Controls on a userform don't have a linked cell property. The linked cell
property on a worksheet is provided by the container: the OLEObject. On a
userform, the Control is the container. The property it provides for this
is named ControlSource. So controlsource on a userform control is
identical in purpose to the linkedcell property in a worksheet activeX
control.

to the best of my knowledge, when you unload a userform, the value in
"linked" cells is not altered. 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. Or if you
hide the userform and then the values in the RowSource in the worksheet for
these boxes is altered, it could cause this problem.

If you are hiding the userform, try unloading it.

--
regards,
Tom Ogilvy



wrote in message
ups.com...
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

controlsource cells in listobox
 

thanks for the prompt response. this is the only code i have..not sure
why the values disappear. any ideas for additional code that can
'freeze' the 'linked' cell values until they are changed through the
form again?

for the list box:
Private Sub Listbox1_Click()
Select Case ListBox1.Value
Case "Product Offering"
ListBox2.RowSource = "InternalForm!Prod"
Case "Communication"
ListBox2.RowSource = "InternalForm!Comm"
End Select
End Sub

and code to launch the form based on a button click
Private Sub CommandButton1_Click()
Userform1.show
End Sub

controlsource cells in listobox
 

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.

--
Regards,
Tom Ogilvy


"yoram" wrote in message
oups.com...
thanks for the prompt response. this is the only code i have..not sure
why the values disappear. any ideas for additional code that can
'freeze' the 'linked' cell values until they are changed through the
form again?

for the list box:
Private Sub Listbox1_Click()
Select Case ListBox1.Value
Case "Product Offering"
ListBox2.RowSource = "InternalForm!Prod"
Case "Communication"
ListBox2.RowSource = "InternalForm!Comm"
End Select
End Sub

and code to launch the form based on a button click
Private Sub CommandButton1_Click()
Userform1.show
End Sub

controlsource cells in listobox
 

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?

controlsource cells in listobox
 

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.

--
Regards,
Tom Ogilvy

"yoram" wrote in message
ups.com...
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?

controlsource cells in listobox
 

ok thanks, i'll try that.

controlsource cells in listobox
 

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

controlsource cells in listobox
 

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