PDA

View Full Version : [SOLVED] Extracting groups of Options button



JKwan
01-25-2019, 09:29 AM
I am looking for ideas as to how best to extract the state of groups of Options button. I will be creating 30 groups of options button with 3 in each group on a sheet. What is the best way to get the state of each group? I have this preliminary sheet that I started. Hopefully, there is an easy way. Thanks.

JimmyTheHand
01-25-2019, 10:08 AM
Maybe

Sub CollectOptionButtonValues()
Dim OpGroups As Collection
Dim Sh As Shape, Ws As Worksheet, o As Object
Dim GroupName As String, GroupValue As String


Set Ws = ActiveSheet
Set OpGroups = New Collection
For Each Sh In Ws.Shapes
Set o = Nothing
On Error Resume Next
Set o = Sh.OLEFormat.Object.Object
On Error GoTo 0
If Not o Is Nothing Then
If LCase(TypeName(o)) = "optionbutton" Then
If o.Value = True Then
GroupName = o.GroupName
Select Case Sh.TopLeftCell.Column
Case 4
GroupValue = "Satisfactory"
Case 5
GroupValue = "Requires Action"
Case 6
GroupValue = "Not Applicable"
End Select
OpGroups.Add GroupValue, GroupName
End If
End If
End If
Next

Debug.Print OpGroups("ERP")
Debug.Print OpGroups("Signage1")
Debug.Print OpGroups("Signage2")
Debug.Print OpGroups("Signage3")
End Sub

JKwan
01-25-2019, 01:24 PM
Thanks Jimmy, this really helps! One question, with the collection, I was trying to get the KEY, so I can get the group name (without what you did at the bottom by entering it). The way I am doing it without the key is that I also put the GroupName into the GroupValue.

JimmyTheHand
01-25-2019, 01:44 PM
It all depends on how you want to use the values. I thought the group names are fixed, and each one of them is used for a unique, specific purpose.
But anyway, if you want to have the group names at hand, I would collect them in an array:


Sub CollectOptionButtonValues()
Dim OpGroups As Collection
Dim Sh As Shape, Ws As Worksheet, o As Object
Dim GroupName As String, GroupValue As String
Dim GroupNames As String, arrGN
Dim i As Long

Set Ws = ActiveSheet
Set OpGroups = New Collection
For Each Sh In Ws.Shapes
Set o = Nothing
On Error Resume Next
Set o = Sh.OLEFormat.Object.Object
On Error GoTo 0
If Not o Is Nothing Then
If LCase(TypeName(o)) = "optionbutton" Then
If o.Value = True Then
GroupName = o.GroupName
Select Case Sh.TopLeftCell.Column
Case 4
GroupValue = "Satisfactory"
Case 5
GroupValue = "Requires Action"
Case 6
GroupValue = "Not Applicable"
End Select
OpGroups.Add GroupValue, GroupName
GroupNames = GroupNames & "," & GroupName
End If
End If
End If
Next
GroupNames = Mid(GroupNames, 2)
arrGN = Split(GroupNames, ",")

For i = LBound(arrGN) To UBound(arrGN)
Debug.Print arrGN(i) & " - " & OpGroups(arrGN(i))
Next
End Sub

JKwan
01-26-2019, 07:30 AM
Thanks again, Jimmy.
Yes, the group names are fixed. I just did not want to type all of them in. I did something similar to what you did by concatenating the Group Name into the GroupValue. I thought that I could access the KEY directly, I just was not able to, so I asked that question.

JimmyTheHand
01-26-2019, 11:48 AM
You're welcome. Sorry if I didn't understand your question (because there was no question mark :devil2:), but anyway I 'm glad you found the solution :)