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!

 

[Jerry Sullivan]

Hi Ed,

You might try using ComboBox.List and/or ComboBox.AddItem to populate your ComboBox instead of RowSource.

Using RowSource with Userform Objects can create some difficulties like the one you describe.

Using those Methods should eliminate the need to use Indirect.

Just ask if you'd like some help with the coding.

 

[ed.ayers315]

Yes, help with the code would be great, thanks.

 

[Norie]

You can do this if you don't use ControlSource.

Just use some simple code to populate the 2nd combobox based on the value selected in the 1st.

Assuming the values in the 1st combobox are named ranges try this.

Private Sub Combobox1_Change()
      If Combobox1.ListIndex<> -1 Then
            Combobox2.List = Range(Combobox1.Value).Value
      End If
End Sub
[code]

 

[ed.ayers315]

Ok

My first Combo is named VESSELS_2A1; this is what I want to use as the source for the named range for the second Combo is named VESSELS_2A1B. I tried this code with these names on the sheet code.

Do I need to write or place it in a module instead of the sheet?

 

[ed.ayers315]

Thanks, I tried my question and used it on the Form sheet code and it works great!

Thanks!!!

 

[Norie]

So what is listed in VESSELS_2A1?

Is it the named ranges that you want to use as the source for VESSELS_2A1B?

That's what I assumed.

I used Combobox1 and Combobox2 in the code I posted because I didn't know the names of your comboboxes.

I also assumed that the comboboxes are on a userform.

 

[ed.ayers315]

Affirmative to all your questions and thanks again for helping me through it.!

 

[Norie]

Well the code belongs in the userform module and you'll need to change the combobox names I used to the ones you are actually using.

Private Sub VESSELS_2A1_Change()
   If VESSELS_2A1.ListIndex<> -1 Then
       VESSELS_2A1B.List = Range(VESSELS_2A1.Value).Value
   End If
End Sub

 

[ed.ayers315]

Hi Norie,

I ran into a snag.

When the user selects the value in the 1st Conbobox "Subject_Title_47C_L1" this is a named range for the second combobox list "EQUIPMENT_48_L1".

The problem is what if the user changes their mind. You have to go back and clear of the sheet values.

I need to be able to select combo 1 and it updates the sheet and when the user tabs or enters it gives the new named range list.

This needs to happen whether the combo 1 already has a value or not.

I tried amending your code to what is below. It works until a value is entered then I am stuck.

Private Sub Subject_Title_47C_L1_Change()
If Subject_Title_47C_L1.ListIndex <> -1 Then
Range("B47:F48").Select
Selection.ClearContents
EQUIPMENT_48_L1.List = Range(Subject_Title_47C_L1.Value).Value
End If
If Subject_Title_47C_L1.ListIndex = -1 Then
End If
End Sub