Function to access Userform selections

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA 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.

VBA Code:
'>>>>>>>>>>>>>>>>>>>>  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"
1656519478649.png
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.

VBA 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.

VBA Code:
'>>>>>>>>>>>>>>>>>>>>  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

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top