Option Button Group name

[phalcon45]

I'm setting up a task list with several hundred tasks (each in its own row as you would imagine) and each with its own task number then for each task a column of status buttons "Complete" "Incomplete" "NotStarted" "Waiting on customer"
I want to use radio buttons to set the status. So because there are so many, I've written a little script that will incrementally assign the LinkedCell for each one so that I dont have to do each one by itself. What I had hoped to do is also have it automatically set the GroupName to the associated task number.
The script works GREAT for the LinkedCell value but it errors out when it tries to set the GroupName.
Im using the same syntax for the GroupName as the LinkedCell (.LinkedCell = XXX and .GroupName = XXX)

Is there something special I have to do for GroupName? I really dont want to have to manually assign hundreds of groups.

 

[phalcon45]

Update
Im able to set the GroupName if I specifically call out the button name "OptionButton1" but if I try to use a variable like Im doing with the LinkedCell, it fails. Is there some trick to calling up an OptionButton using a variable?

 

[DanteAmor]

Check the following example to create 2 optionbutton groups, in rows 6 and 7, each group with different group name

Sub Group_name()
    Dim sh As Worksheet
    Dim obj As OLEObject
    Dim wRow As Long, wCol As Long, i As Long
    Dim status As Variant
    
    Set sh = ActiveSheet
    For wRow = 6 To 7
        wCol = 4
        status = Array("Complete", "Incomplete", "NotStarted", "Waiting on customer")
        For i = 0 To UBound(status)
            Set obj = sh.OLEObjects.Add(ClassType:="Forms.OptionButton.1")
            With obj
                .Select
                .Top = Cells(wRow, wCol).Top + 3
                .Left = Cells(wRow, wCol).Left + 3
                .Name = "ob_" & wRow & wCol
                .LinkedCell = Cells(wRow, wCol)
            End With
            sh.OLEObjects("ob_" & wRow & wCol).Object.Caption = status(i)
            sh.OLEObjects("ob_" & wRow & wCol).Object.GroupName = "group_" & wRow
            wCol = wCol + 1
        Next
    Next
End Sub

It would look like this:

Let me know if you have any doubt.

 

[phalcon45]

Thank you. We're not quite there with it. I've already got the buttons established and the code I've got has already assigned all the linked cells. Now I just need it to rename the group based on the task number. Hoping it can be something simple...
Is there a quick way to call up the buttons by name (OptionButton1 then OptionButton2 then 3 etc) and just change the GroupName?

 

[DanteAmor]

Thank you. We're not quite there with it. I've already got the buttons established and the code I've got has already assigned all the linked cells. Now I just need it to rename the group based on the task number. Hoping it can be something simple...
Is there a quick way to call up the buttons by name (OptionButton1 then OptionButton2 then 3 etc) and just change the GroupName?

Actually I do not know how you have your optionbutton, I was just trying to show you how to put the GrorupName:

[COLOR=#333333]sh.OLEObjects("ob_" & wRow & wCol).Object.GroupName = "group_" & wRow[/COLOR]

If you have already created the optionbutton, then select the optionbutton and give it a name.
Continuing with my example:

Sub Change_Group_name()
    Dim sh As Worksheet
    Dim wRow As Long, wCol As Long, i As Long
    
    Set sh = ActiveSheet
    For wRow = 6 To 7
        wCol = 4
        For i = 1 To 4
            sh.OLEObjects([COLOR=#ff0000]"ob_" & wRow & wCol[/COLOR]).Object.GroupName = "gName" & wRow
            wCol = wCol + 1
        Next
    Next
End Sub

 

[phalcon45]

Thanks I really appreciate it. For some reason though it still gives me an error as soon as it hits the sh.OLEObjects line. Not sure whats happening.

 

[DanteAmor]

Thanks I really appreciate it. For some reason though it still gives me an error as soon as it hits the sh.OLEObjects line. Not sure whats happening.

In the part in red you should go the name of the optionbutton, remember that this goes according to the name of my example, but there you must put the name of your optionbutton.

[COLOR=#333333]sh.OLEObjects([/COLOR][COLOR=#ff0000]"ob_" & wRow & wCol[/COLOR][COLOR=#333333]).Object.GroupName = "gName" & wRow[/COLOR]

----
It would help a little if you put the name of your optionbutton or the way you created them.

 

[Norie]

Phalcon45

Can you post the code you are using to create the option buttons/linked cells?

PS Have you considered any another approachs? For example a small dropdown in each row for the status of each task.

 

[DanteAmor]

Phalcon45

For example a small dropdown in each row for the status of each task.

That's a better idea than creating hundreds of optionbuttons, because creating is relative, another problem is inserting rows, deleting rows, ordaining data, etc.