Function to access Userform selections

[GeeWhiz7]

Hi Folks,

Excel Max has helped me understand a useful way to link option button choices in a userform to a Case SELECT inside my vba code. This code takes the choice of option box and returns it when the ActiveOption function is called from the userform code.

Function ActiveOption()
   vA = Array("optCenter", "optAbove", "optRight", "optBelow", "optLeft")
   For vN = 0 To UBound(vA)
      If Controls(vA(vN)) Then ActiveOption = vA(vN): Exit Function
   Next vN
End Function

I then use the choice in CASE to execute some code like the snip below.

'>>>>>>>>>>>>>>>>>>>>  ActiveOption Function Call  <<<<<<<<<<<<<<<<<<<<<
      Select Case ActiveOption
         Case "optAbove"
            .VerticalAlignment = xlVAlignBottom

This works fine with option buttons, but I am struggling to figure out

• how to use the same "Function" approach on a Combobox or other object/multiple objects.
• how to have multiple uses of this in one userform. I get an error object not found shown in the image below when I try to do something similar on a combobox.
• I am trying to use this approach on three selection areas in my userform (two separate option boxes inside frames and one combobox), but past using only one, I get an error "could not find specified object"

 

[GeeWhiz7]

Hi Folks,

Excel Max has helped me understand a useful way to link option button choices in a userform to a Case SELECT inside my vba code. This code takes the choice of option box and returns it when the ActiveOption function is called from the userform code.

Function ActiveOption()
   vA = Array("optCenter", "optAbove", "optRight", "optBelow", "optLeft")
   For vN = 0 To UBound(vA)
      If Controls(vA(vN)) Then ActiveOption = vA(vN): Exit Function
   Next vN
End Function

I then use the choice in CASE to execute some code like the snip below.

'>>>>>>>>>>>>>>>>>>>>  ActiveOption Function Call  <<<<<<<<<<<<<<<<<<<<<
      Select Case ActiveOption
         Case "optAbove"
            .VerticalAlignment = xlVAlignBottom

This works fine with option buttons, but I am struggling to figure out

• how to use the same "Function" approach on a Combobox or other object/multiple objects.
• how to have multiple uses of this in one userform. I get an error object not found shown in the image below when I try to do something similar on a combobox.
• I am trying to use this approach on three selection areas in my userform (two separate option boxes inside frames and one combobox), but past using only one, I get an error "could not find specified object"

View attachment 68225

I have figured out how to control the userform combobox

Function ActiveCombo()
   vA = Array("General", "a", "b", "c", "d", "e", "f", "Data")
   For vN = 0 To UBound(vA)
      If Helper.Controls("DisCombo") = (vA(vN)) Then ActiveCombo = vA(vN): Exit Function 'Helper is name of userform, DisCombo is the combobox name
   Next vN
End Function