VBA - Autofill Combo boxes based on option button choice

[ZedsDead]

Hi all!

I'm pretty new to VBA and was wondering if anyone could help me out with a problem I'm having!

I've got two choices that have been defined in my combo boxes, L and R. I've got quite a few combo boxes so instead of going through every combo box and selecting the same option, for the cases where this is true, I want to be able to check L or R on the relevant option button and autofill the combo boxes with the choice of L or R.

Thanks in advance for taking a look over this!

 

[MickG]

Assuming the controls are ActiveX then
Use this code in one of the option Buttons.
NB:- This is a change event , not a click event

Private [COLOR=Navy]Sub[/COLOR] OptionButton1_Change()
[COLOR=Navy]Dim[/COLOR] Cb [COLOR=Navy]As[/COLOR] OLEObject
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Cb [COLOR=Navy]In[/COLOR] ActiveSheet.OLEObjects
   [COLOR=Navy]If[/COLOR] TypeName(Cb.Object) = "ComboBox" [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] OptionButton1 [COLOR=Navy]Then[/COLOR]
            Cb.Object.ListIndex = 0
        [COLOR=Navy]Else[/COLOR]
            Cb.Object.ListIndex = 1
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Cb
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[ZedsDead]

Hi Mick,

Thanks for getting back to me!

The controls I'm using aren't ActiveX unfortunately. I'm working within a user form with the controls toolbox. Is there any way I can use these tools to achieve the same goal?

Regards,

ZD

 

[MickG]

Try this in your Userform Module.

Private Sub OptionButton1_Change()
Dim Cb As Control
For Each Cb In Me.Controls
   If TypeName(Cb) = "ComboBox" Then
        If OptionButton1 Then
            Cb.ListIndex = 0
        Else
            Cb.ListIndex = 1
        End If
    End If
Next Cb
End Sub

 

[ZedsDead]

Hi Mick,

Thank you very much for your help. It's worked a treat!!

Regards,

ZD

 

[MickG]

You're welcome