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

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

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:


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

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
Sub x()
MsgBox Sheets(1).OptionButton1.GroupName
Sheets(1).OptionButton2.GroupName = Sheets(1).OptionButton3.GroupName
End Subwith this you could easy manage the Option Buttons and their properties... like if they were inside an userform

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

This is an example of what I meant...

Hey Rick

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


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

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?

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.

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:Public WithEvents Button As MSForms.OptionButton

Private Sub Button_Click()

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

End SubThen, 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.)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 SubAfter you've run Init(), try clicking on one of your optionbuttons.

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

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

Wayne

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.

Or add an outer loop:Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each ole In ws.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

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

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 (http://www.visualbasicbooks.com/progVB6samplepg1.html)

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

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

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.

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.

Try this:
If AllButtons Is Nothing

I was using if allbuttons = nothing

Thanks once again

Wayne