Excel forms combo box problem

[ed.ayers315]

Hi Folks,

I have a combo box that is 1st in the tab order Control Source c15, the second combo box RowSource is set to Indirect(c15).

I have to close the form before c15 is updated with the new value before my indirect function works.

Is there something I can do to stay in the form view and get c15 updated with the selected value?

Thanks for any help!

 

[Norie]

Why do the sheet values need to be cleared?

Aren't they the values being used to populate the comboboxes?

 

[ed.ayers315]

When the user has the form open and decides to change the combo 1 selection, it was not updating the range that the combo 2 box needs.

 

[Norie]

Sorry I'm not following.

Do you meant when the use selected something in combo1 then combo2 was not populated?

 

[ed.ayers315]

Yes, I would have to close a reopen before the change occured.

I have tried the following code and it works unti the second set. These are with the countolsources left blank as I read in one of the posts.

I have 3 of these combination on one form page and 4 on another.

Private Sub Subject_Title_47C_L1_Change()
If Subject_Title_47C_L1.ListIndex <> -1 Then
End If
EQUIPMENT_48_L1.List = Range(Subject_Title_47C_L1.Value).Value

End Sub

Private Sub Subject_Title_50C_L1_Change()
If Subject_Title_50C_L1.ListIndex <> -1 Then
End If
Equipment_51_L1.List = Range(Subject_Title_50C_L1.Value).Value

End Sub
Private Sub Subject_Title_53C_L1_Change()
If Subject_Title_53C_L1.ListIndex <> -1 Then
End If
Equipment_54_L1.List = Range(Subject_Title_53C_L1.Value).Value

 

[Norie]

Right I think I understand what you mean there, though I don't know why that's happening.

I can't see anything wrong with the code, apart from the End If being in the wrong place, it should go after the line to populate the other combobox.

Like this:

Private Sub Subject_Title_47C_L1_Change()
    If Subject_Title_47C_L1.ListIndex <> -1 Then
 
        EQUIPMENT_48_L1.List = Range(Subject_Title_47C_L1.Value).Value

    End If

End Sub

What I now don't understand is how the comboboxes would be populated properly when you close and reopen the form.

When you close the form they should lose their values.

Is there anything else going on?

Are you using RowSource?

Oh, and I still don't know why you need to clear a range on the worksheet.

 

[ed.ayers315]

Yes, to rowsource is Indirect(b47) for hte first, (b50) 2nd, and (b53) 3rd.

If I do not use the controlsource the sheet does not update and stays blank in those cells.

If I take out the code we have been discussing the combo box controlled by the indirect does not update to the new value in the first combo box.

 

[Norie]

You don't need to use the RowSource if you are populating the comboboxes using the named ranges via their List property.

 

[ed.ayers315]

So the CB gets the reference from code instead of the property box?

How would that be coded?

 

[ed.ayers315]

Norie,

I finally figured out why I confused you, because I am clearly confussed myself.

I removed the rowsource and placed the controlsource back for both.

I cleaned up the problem; thanks for sticking in there and getting me to this point.

Thank you!

 

[Norie]

It's already coded to to do that.

This populates EQUIPMENT_48_L1 with a named range, taking the name of the named range from the value in Subject_Title_47C_L1.

EQUIPMENT_48_L1.List = Range(Subject_Title_47C_L1.Value).Value