Option Button groupname - Xtreme Visual Basic Talk

10-26-2004, 09:58 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Option Button groupname

I have a few worksheets with VBA option buttons on them. They are not the forms option buttons nor are they on a user form. I realize to access some properties of these I have to access them as a shape, but I cannot see the groupname property. Is there some way of doing do?

Thanks

Wayne

10-26-2004, 10:38 AM

rick_deacha

Sith Lord

Retired Leader
* Expert *

Join Date: Feb 2004

Location: Monterrey, Mexico

Posts: 2,179

Welcome to the forum Wayne...

The controls from the Forms Toolbar could be useful for an "only Excel user", they are easy to set, configure and use. But they could be painful when you try to code them.

Try using the controls from the "Control Toolbox" Toolbar, you could find every event related to them in the Worksheet Module in which they are. And yes the Option Button from this Toolbar has the GroupName property.

__________________
Rick
Use [vb][/vb] | Refer to VBA or VB | Newbie? run the Macro Recorder
Excel and VB Automation :|: Excel FAQ

10-26-2004, 11:24 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Rick

I have used ones from the control toolbox, but since they are being used on a worksheet rather then on a uerform the properties are not easily accessed. I have found some code to so this but still need some other ways as I want to pass group names to other functions in VBA. The code I have found that works as follows:

Code:

Sub SetValues()

    Dim Shp As Shape
    Dim Ans As String
    Dim ControlVal As String
    Dim CtlGrp As String
    Dim StoredAns As String
    
    
    For Each Ole In ActiveSheet.OLEObjects
        Set ctl = Ole.Object
        If TypeOf ctl Is msforms.OptionButton Then
            CtlGrp = ctl.GroupName
            Ans = CtlGrp + "Answer"
            'StoredAns = Ans
            StoredAns = Sheet8.Range(Ans)
            ' Look at the answer that is stored on the Summary Sheet and set the value
            ' of the control accordingly
            Select Case StoredAns
                Case Is = "Now"
                    If Ole.Name = CtlGrp + "Yes" Then
                        ctl.Value = True
                    Else
                        ctl.Value = False
                    End If
                    
                Case Is = "Within 9 Months"
                    If Ole.Name = CtlGrp + "9M" Then
                        ctl.Value = True
                    Else
                        ctl.Value = False
                    End If
                Case Is = "No"
                    If ctl.Name = CtlGrp + "No" Then
                        ctl.Value = True
                    Else
                        ctl.Value = False
                    End If
                Case Is = ""
                    ctl.Value = False
                    
                Case Else
                    MsgBox "Error"
            End Select
        End If
    Next Ole

End Sub

10-26-2004, 11:41 AM

rick_deacha

Sith Lord

Retired Leader
* Expert *

Join Date: Feb 2004

Location: Monterrey, Mexico

Posts: 2,179

I see your point, but since they are not in an userform, they had to be somewhere else... like I said, every event from this option buttons stays on the Worksheet Module in which they are contained... so they are objects in that sheet...

As an example I put 3 Option buttons in Sheet1, and I changed every GroupName... then I put this in a procedure

Code:

Sub x()
    MsgBox Sheets(1).OptionButton1.GroupName
    Sheets(1).OptionButton2.GroupName = Sheets(1).OptionButton3.GroupName
End Sub

with this you could easy manage the Option Buttons and their properties... like if they were inside an userform

__________________
Rick
Use [vb][/vb] | Refer to VBA or VB | Newbie? run the Macro Recorder
Excel and VB Automation :|: Excel FAQ

10-26-2004, 01:15 PM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Thanks Rick

The problem is I do not want to refer or cannot in some cases, like the sample I posted I want cycle through them all. In another case I want one generic routine, then when a user clicks on the option button I can look at the groupname and then tabulate results based on that.

Example
Sheet has 10 questions with each having 3 parts
I use a naming convention for the group such as Q1a, Q1b, the object name will contain the value, Q1aNo, Q1aYes, Q1aNA. from this I can derrive the answer (obvious) but programically since these properties are not as easliy accessed as they would be on a form, that is the problem.

I would like to on selection find the control name and groupname that was selected, then I can update a summary sheet. The purpose of this is a customer survey.

With this in mind I have tried the ole.groupname as I posted, this is ok as long as it follows for each ole in activesheep.oleobjects as well as a set command
without I get an error 91 runtime object error.

Wayne

10-26-2004, 02:25 PM

rick_deacha

Sith Lord

Retired Leader
* Expert *

Join Date: Feb 2004

Location: Monterrey, Mexico

Posts: 2,179

This is an example of what I meant...

Attached Files

OptionButtons.zip (10.4 KB, 9 views)

__________________
Rick
Use [vb][/vb] | Refer to VBA or VB | Newbie? run the Macro Recorder
Excel and VB Automation :|: Excel FAQ

10-27-2004, 07:41 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Hey Rick

I got you there, but I wanted to perform something like this

Code:

Private Sub Q1aNo_Click()
    Call SetValue("Q1aN")
End Sub

Sub SetValue(controlref)
set some values here on a summary sheet, the control reference is passed for each
control, there are 300 or so on 6 sheets.

end sub

Ideally I would just like to call the sub without referencing the control. Then in the sub I could capture the objects group name as it references the question, then I can determine where to store the value.

I guess I am going to have to pass the variable after all.

Wayne

10-27-2004, 01:43 PM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

I don't quite understand the problem, I'm afraid... Are you saying that you want all the 300 option buttons to call the same sub, but with a different parameter?

10-27-2004, 02:00 PM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen

They would call the same sub, I want the sub to look at what optionbutton was pressed. I have grouped them by question number Q1, Q2 etc and named them according to the answer they represent Q1No, Q1Yes etc. The sub will look at the group name or the name and then find the Answer selected and record it on a summary sheet with a named list for each question.

Easy right LOL. The problem is accessing the groupname property because they are not on a userform they are treated like shapes. If I want to do a mass update of the values that is easy:
For Each Ole In ActiveSheet.OLEObjects
Set ctl = Ole.Object
If TypeOf ctl Is msforms.OptionButton Then ctl.Value = False end if
next Ole

However if I try to reference this outside the for loop an error is generated.

#10

10-27-2004, 02:15 PM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

OK, I see what you mean.

The problem here is that there is no way for the SetValue sub to know which button called it. You need a bit of a workaround. You need a class module that can handle all the events, and at the same time keep track of which particular button was clicked.

Try this. Add a class module to your project and call it EventCatcher. Add this code to the class module:

Code:

Public WithEvents Button As MSForms.OptionButton

Private Sub Button_Click()

    MsgBox "You clicked " & Button.Caption & " which belongs to group " & Button.GroupName

End Sub

Then, in a standard module, put this code, and run it. (You'll have to change the sheet reference, so it refers to the sheet with the buttons.)

Code:

Public AllButtons As Collection

Sub Init()

    Dim ole As OLEObject
    Dim ec As EventCatcher
    
    Set AllButtons = New Collection
    For Each ole In Sheet1.OLEObjects
        If TypeOf ole.Object Is MSForms.OptionButton Then
            Set ec = New EventCatcher
            Set ec.Button = ole.Object
            AllButtons.Add ec
        End If
    Next ole

End Sub

After you've run Init(), try clicking on one of your optionbuttons.

#11

10-27-2004, 05:32 PM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen that is great except I have 7 sheets.....

Could I cycle through all of them with the init code?

Wayne

#12

10-28-2004, 04:30 AM

Timbo

Green-Eyed

Super Moderator
* Guru *

Join Date: May 2001

Location: Bangkok, Thailand

Posts: 10,261

Sure, but since you can't actually click a button unless it's on the active sheet, you should run the routine as each sheet is activated. Good practice would also suggest that you erase the previous objects from the collection before loading the new ones.

__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional

#13

10-28-2004, 06:28 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

Or add an outer loop:

Code:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For Each ole In [b]ws[/b].OLEObjects
        If TypeOf ole.Object Is MSForms.OptionButton Then
            Set ec = New EventCatcher
            Set ec.Button = ole.Object
            AllButtons.Add ec
        End If
    Next ole
Next ws

#14

10-29-2004, 08:55 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen

I copied the code as you had in a new sheet just to try as I am not familiar with Class Modules. You cannot name a class module as you say. And when I tried to run the init() sub
I receive the following on the line Dim ec as EventCatcher
User Defined Type not Defined

Sorry I'll plead ignorance on Class Modules I am reading on them now in MSDN.

Wayne

#15

10-29-2004, 09:00 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

To change the name of a class module (after you've done Insert > Class module):
Bring up the Properties window, if it is not visible. (Hit F4 in the VBE, or choose View > Properties Window.)
Select your newly-created class in the project explorer.
The topmost item in the Properties window should now say: (Name) Class1. Change that to whatever you prefer.

Alternatively, of course, you could change the code that it refers to Class1 instead of EventCatcher... but in the long run that's not a very good approach to take, since it will become very confusing. That's like calling all your procedures Sub1, Sub2, Sub3 etc...

PS. Here is a very thorough explanation of what classes are all about

#16

10-29-2004, 11:03 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen

Thanks a bunch, in playing around with this it also removes a lot of code, I no longer have to have a click event for each calling the sub to record the values.

Thanks Again!!

Wayne

#17

11-03-2004, 09:37 AM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen

I have come across one small issue when testing this. Since the init() sub runs when the workbook opens, if a user clicks on the VB Editor button that code is flushed from memory and the button click event will not run. Is there a workaround for this somehow. I have tried simply protecting the VB code and this does not work.

Thanks

Wayne

#18

11-03-2004, 10:12 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

You could use the Worksheet_Activate (or Workbook_Activate) event to check if the AllButtons collection exists, and if it doesn't, rerun Init. There's still a small chance that the user will happen to click End at the worst possible moment (just AFTER the workbook_activate) so there's still a minor chance that the code won't run... but in that case they can just switch to a different sheet/workbook, and switch back, and it will work again.

#19

11-03-2004, 06:54 PM

Kibbs101

Newcomer

Join Date: Sep 2004

Posts: 19

Helen I have looked at how one would check if the collection exists. If I try use .count and the stop, start action has been performed then the collection no longer exists, the only way I can see is trapping that error and if if happens in the error routine run the init() sub.

#20

11-04-2004, 04:05 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

Try this:
If AllButtons Is Nothing