excel - OptionButton index issue

Question

I have five option button(Form control) on first five cell of each row. The option button are linked to sixth cell respectively for each row and i want to get the index number in sixth cell.

(The Option Button Form Control returns the value of the Option Button indicating its status to a linked cell.)

Now issue is from the second row onwards the index number of option button is greater than 5 so the value in sixth cell is greater than 5. I want them to be between 1 to 5.

(Index is readonly property of OptionButton class)

How can this be achieved ? (preferably without vba)

Thanks for looking into it.

Answer 1

AS stated in the Comment using a GroupBox Around each set of Buttons will allow each set to operate independently of each other. In the picture below I removed the GroupBox Names and lined the boarder of the groupboxes with the cell boarders then used cell boarders to almost mask the groupbox so you can't tell they are even there except for the one that is actively selected (the top most one):

On request here is some VBA code. I would suggest that before running this code so set the height of the rows to a minimum of 20 points. AS the groupbox minimum height is 19.5, Also the width of the cell will beused to determine the width of the OptionButtons, so make sure to use a width that will be roomy enough for the text of the OptionButton.

Sub Sample()

Dim Top As Variant, Left As Variant, Height As Variant, Width As Variant
Dim rngActiveRowA As Range, rngEndOfBox As Range
Dim lngActiveRow As Long, lngActiveColumn As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

For lngActiveRow = 1 To 5

Set rngActiveRowA = Range("A" & lngActiveRow)

Set rngEndOfBox = Range("F" & lngActiveRow + 1)

Top = rngActiveRowA.Top
Left = rngActiveRowA.Left
Height = rngEndOfBox.Top - Top
Width = rngEndOfBox.Left - Left

ActiveSheet.GroupBoxes.Add(Left, Top, Width, Height).Caption = ""

    For lngActiveColumn = 1 To 5

    With ActiveSheet
        Top = .Cells(lngActiveRow, lngActiveColumn).Top
        Left = .Cells(lngActiveRow, lngActiveColumn).Left
        Height = .Cells(lngActiveRow + 1, lngActiveColumn + 1).Top - Top
        Width = .Cells(lngActiveRow + 1, lngActiveColumn + 1).Left - Left
    End With

    With ActiveSheet.OptionButtons.Add(Left, Top, Width, Height)
        .Characters.Text = "OB" & lngActiveColumn
        .LinkedCell = "$F$" & lngActiveRow
    End With

    Next lngActiveColumn

Next lngActiveRow


With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

End Sub

This code will create a groupbox around the first 5 columns of each row for the first 5 rows. It will also populate a Option Button within each cell of that area linked to the F cell in its respective row. and Rename each button to OB + the column number it is in.