Problem with OptionButton

Question

I have five option buttons (form control) in the first five cells of each row. The selection button is associated with the sixth cell for each row, respectively, and I want to get the index number in the sixth cell.

(The Button Form Control option returns the value of the Option button indicating its status on the associated cell.)

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

(Index is the readonly property of the OptionButton class)

How can this be achieved? (preferably without vba)

Thanks for looking at him.

Answer 1

AS referenced by GroupBox. In all sets of buttons, each of them will work independently of each other. In the screenshot below, I deleted the GroupBox names and aligned the group box borders with border cells, and then used the cell borders to almost mask the group box, so you cannot say that they are there except the one that is actively selected (the top one ):

Upon request, VBA code is provided here. I would suggest that before running this code set the line height to at least 20 points. AS the minimum height of the group box is 19.5. Also, the width of the cell will be used to determine the width of the OptionButtons, so be sure to use a width that is wide enough for OptionButton text.

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 group box around the first 5 columns of each row for the first 5 rows. It will also populate the Option button in each cell of this area, associated with cell F in the corresponding row. and rename each button to OB + the column number in which it is located.