INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Are Option Button Arrays possible in Excel?

Are Option Button Arrays possible in Excel?

(OP)
Is there a way to find out which option button on a worksheet is selected by looping through an option button array?  I remember doing something like this in VB5.  I'm trying to do the same in VBA in Excel now but can't make the button names an array.  Right now I have to check each button individually for a true value.  Works OK with 2 or 3 buttons but with more it gets cumbersome.


 If OptionButton1 Then
        gSystem = 1
    ElseIf OptionButton2 Then
        gSystem = 2
    Else
        gSystem = 3
    End If


Can't you do something like this?

 For i = 1 to 3
    if optionbutton(i) Then
        gSystem = i
    end if
Next i

RE: Are Option Button Arrays possible in Excel?




Sheet controls are SHAPES.  They are included in the Sheet Object Shapes Collection

CODE

dim shp as shape

for each shp in Sheets("Sheet1").shapes
  with shp
    select case .name
       case "OptionButton1"
          gSystem = 1
       case "OptionButton2"
          gSystem = 2
       case "OptionButton3"
          gSystem = 3
    end select
  end with
next
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Are Option Button Arrays possible in Excel?

(OP)
I'm not sure that helps.  You're still referring to each specifically with the select case statement.  Can you consider the shapes an array and find the one with that is selected?

maybe something like (syntax may be wrong since I haven't tried in Excel):

CODE

for each shp in Sheets("Sheet1").shapes
  if shp.value then
      gSystem = right$(shp.name,1)
  end if
next

RE: Are Option Button Arrays possible in Excel?




You can do that.  However, be aware that there may be other shapes on the sheet.  So a better approch might be...

CODE

for each shp in Sheets("Sheet1").shapes
  With shp
    if Left(shp.name,12) = "OptionButton" then
      gSystem = mid$(shp.name,13)
    end if
  end with
next
you really should not use right 1, because it is possible to have more than 9 option buttons
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Are Option Button Arrays possible in Excel?

(OP)
Thanks.  Also thanks for the comment about right$.  I would have found that but only after the code broke when I added more option buttons blush

RE: Are Option Button Arrays possible in Excel?




Always good to get a "yea-but" optinion.  winky smile

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close