Combobox: Optionbutton dependent lists

[nuno_coelho]

First ever post on this forum so hello to all!

I have been learning about VBA through web resources and am now starting to experiment with it in my job.

I ran into the first problem that I haven't been able to solve for myself so I am submitting it here in the hopes that somebody can help me out understand why the code is not working.

I have a userform which looks like this:

userform.jpg

I want the combobox "Departamento" to display a certain list which will depend on which of the option buttons is activated. I.e., if the option button BIC is activated, a specific BIC-list appears on the combobox "departamento" below it. If Pro-Organica or BIB is activated, then another list will appear as input for the combobox "departamentos".

As code for my combobox "Departamento", I have this:

Private Sub cb_depto_Click()


If Me.ob_bic = True Then
Me.cb_depto.RowSource = "lista_centrosBIC"
ElseIf Me.ob_bib = True Or Me.ob_PO = True Then
Me.cb_depto.RowSource = "lista_centrosBIB"
End If


End Sub

So, pretty straightforward. A click event on the combobox activates a certain RowSource property depending on which option button is considered as TRUE.

Problem is, nothing happens. No error or other, but it always remains blank.

I can't seem to figure out what is wrong in my coding logic.

Could any one help me understand this?

Thanks a lot in advance.

-Nuno

[SamT]

The two listas are Named Ranges,yes?

[nuno_coelho]

Yes, they are.

[mikerickson]

The _Click event fires when you change the selection in the combo box. The place to put that code would be in the option button code.

Private Sub ob_bic_Click()
    If Me.ob_bic = True Then
        Me.cb_depto.RowSource = "lista_centrosBIC"
    ElseIf Me.ob_bib = True Or Me.ob_PO = True Then
        Me.cb_depto.RowSource = "lista_centrosBIB"
    End If
End Sub

Private Sub op_bib_Click()
    Call op_bic_click
End Sub

[SamT]

Private Sub cb_depto_Click() 'Bad, bad, bad. This Click is also used to change the Value

RowSource = lista_centrosBIC
OR
RowSource = lista_centrosBIB

[SamT]

The problem with putting the Call in one OptionButton is that the sub won't trigger if the User clicks another button. Putting the Code in all buttons will work.

Personally, I would put sub Calls in the OptionButtons Click event Procedures.

In Design Mode set cb_depto.RowSource to the preferred List, and the preferred Default_Button.Value = True. Then

Private Sub ob_bic_Click() 'And all other buttons
SetListos
End SUb

Private Sub SetListos() 

    If Not Me.ob_bic Then
       Me.cb_depto.RowSource = lista_centrosBIB 
    Else: Me.cb_depto.RowSource = lista_centrosBIC
    End If
End Sub

This allows:
The List Control is always filled
If the User likes the Default, they can skip clicking any OptButton
If the User changes their mind many times, the List is always current

[SamT]

It may be the situation where you have many Options and many Lists

In Design Mode set cb_depto.RowSource to the preferred List, and the preferred Default_Button.Value = True. Then:
(All OptButtons in a Frame!)

Private Sub ob_bic_Click() 
    Me.cb_depto.RowSource = lista_centrosBIC
End Sub

Private Sub ob_bib_Click() 
    Me.cb_depto.RowSource = lista_centrosBIB
End Sub

Private Sub ob_PO_Click() 
    Me.cb_depto.RowSource = lista_centrosPO
End Sub

[nuno_coelho]

Hi,

Thanks for all your replies.

So, it seems that for some reason the _click event on the combobox is not valid for this code, even if I still am not clear on the reason why.

I adapted the code to SamT's last suggestion, which seems the simplest.

Thanks for your support.

If anyone can explain to me in simple terms why the _click event on the combobox cannot apply to this code, I'm very interested to know.

[SamT]

If anyone can explain to me in simple terms why the _click event on the combobox cannot apply to this code,

What will happen is that every time the Combobox is clicked, it will reload the list. That is just bad programming and I have never tried it to see if the selected Item is still Selected after the List is reloaded, but I doubt it.

A click event on the combobox activates a certain RowSource property depending on which option button is considered as TRUE.

Problem is, nothing happens. No error or other, but it always remains blank.

I think that proves my doubt.