Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
controlsource cells in listobox
ok thanks, i'll try that.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
controlsource cells in listobox
nevermind...i've got it to work. thanks again for your help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlsource errors | Excel Programming | |||
ControlSource | Excel Programming | |||
resetting controlsource cells | Excel Programming | |||
Controlsource | Excel Programming | |||
ControlSource Compatibility VBA - VB6 | Excel Programming |