OptionButton索引问题 [英] OptionButton index issue

问题描述

我在每一行的前五个单元格上有五个选项按钮（表单控件）。选项按钮分别链接到每行
的第六个单元格，我想在第六个单元格中获取索引号。

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.

（Option Button Form Control返回选项按钮的值指示其链接的单元格的状态。）

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

现在问题是从第二行开始，选项按钮的索引号大于5
，所以第六个单元格的值大于5.我希望它们在1到5之间。

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.

（索引是OptionButton类的只读属性）

(Index is readonly property of OptionButton class)

如何实现？ （最好没有vba）

How can this be achieved ? (preferably without vba)

感谢您查看。

推荐答案

在使用GroupBox的评论中声明的AS围绕每组按钮将允许每个组彼此独立运行。在下面的图片中，我删除了GroupBox名称，并将单元格寄存器的边框与边框对齐，然后使用单元格寄存器几乎掩盖了分组框，因此除了主动选择的那个外大多数）：

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):

根据要求，这里有一些VBA代码。我建议在运行此代码之前，将行的高度设置为至少20点。因为groupbox的最小高度是19.5，还可以使用单元格的宽度来确定OptionButtons的宽度，所以一定要使用宽度足够大的OptionButton的文本。

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

此代码将围绕前5行的每行前5列创建一个组框。它还将在其相应行中的F单元链接的该区域的每个单元格内填充选项按钮。并将每个按钮重命名为OB +其中的列号。

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.

这篇关于OptionButton索引问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！