选项按钮验证框架

Question

我在frame1中有两个optionbuttons。

ON ON ON ON ON ON按钮将收集的数据传输到Excel Sheet1中，我编写了以下代码，该代码实际上检索了MSG，但它不允许完成从Userform objets到Sheet1

的数据传输。

Dim ctrl As Control
Dim fr1 As Boolean
For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.OptionButton Then
        Select Case ctrl.Parent.Name
        Case "Frame1"
            If ctrl.Value = True Then fr1 = True
        End Select
    End If
Next ctrl
If fr1 = False Then MsgBox "No selection for option buttons in Frame1"
Exit Sub

如何验证optionbuttons进入帧1，而不会中断将数据发送到Sheet1？

Answer 1

将此代码添加到您的用户形式，并查找'＆lt;＆lt;＆lt;自定义>>>部分

另外，请检查代码中的评论。

Option Explicit
Function ValidateOptions() As Boolean
    ' Declare objects
    Dim ctrl As Control
    ' Declare other variables
    Dim oneOptionSelected As Boolean
    ' Loop through each of the forms' controls
    For Each ctrl In Me.Controls
        ' If the control is an option button
        If TypeOf ctrl Is MSForms.OptionButton Then
            ' If it's parent is an specific frame
            If ctrl.Parent.Name = "Frame1" Then
                oneOptionSelected = ctrl.Value
                ' If any of the option buttons is selected
                If oneOptionSelected = True Then Exit For
            End If
        End If
    Next ctrl
    ' If at least one of the option buttons was checked
    If oneOptionSelected = True Then
        ValidateOptions = True
    Else
        MsgBox "No selection for option buttons in Frame1"
    End If
End Function
Private Sub CommandButton1_Click()
    ' Declare objects
    Dim targetSheet As Worksheet
    ' Declare other variables
    Dim targetSheetName As String
    Dim emptyRow As Long
    Dim cancel As Boolean
    ' <<< Customize this >>>
    targetSheetName = "Sheet1"
    ' Validate options
    If ValidateOptions = False Then Exit Sub
    ' Initialize objects
    Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)
    ' Determine emptyRow
    emptyRow = WorksheetFunction.CountA(targetSheet.Range("A:A")) + 1 ' This code is not reliable. Look for solutions on how to find next empty row here in SO
    If Me.OptionButton1.Value = True Then
        targetSheet.Cells(emptyRow, 1).Value = "Accepted"
    Else
        targetSheet.Cells(emptyRow, 2).Value = "Rejected"
    End If
End Sub