需要注意的是，该代码仅适用于表单的基本信息部分和使用function ReturnFormControlCaption__的Hazardous或Non-Hazardous的2个选项按钮。你可以把剩下的数据放在硬码中(或多或少只是复制粘贴，重命名变量，调整范围值，并将变量添加到数组中)。

Sub BBUEntryToDatabase()

Dim CustCompany As String
Dim CustName As String
Dim CustLocation As String
Dim CMTRep As String
Dim QuoteNo As String
Dim QuoteDate As String
Dim Hazard as String

With ThisWorkbook.Sheets("BBU Quote Entry")

    CustCompany = .Range("D6").Value
    CustName = .Range("D8").Value
    CustLocaction = .Range("D10").Value
    CMTRep = .Range("G6").Value
    QuoteNo = .Range("G8").Value
    QuoteDate = .Range("G10").Value
    Hazard = ReturnFormControlCaption("BBU Quote Entry", "HazardousButton", "NotHazardousButton")

End With

Dim BBUArray As Variant
'The Array is assigned in order of your headings on "BBU Quote Database" sheet
BBUArray = Array(QuoteNo, CustCompany, CustName, CustLocation, CMTRep, QuoteDate, _
 "Clearance", "Height", "Material", "Density", Hazard) 

Dim Destination As Range
Dim LastRow As Long

    With ThisWorkbook.Sheets("BBU Quote Database")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        Set Destination = .Cells(LastRow, 2)
        Set Destination = Destination.Resize(1, UBound(BBUArray, 1) + 1) ' + 1 as the array is 0 based (whereas columns start at 1).
        Destination.Value = BBUArray
    End With

End Sub

这里是我的数据输入的屏幕截图

和"BBU报价数据库“上的输出(使用相同的输入进行了3次测试后)

我对Form Controls不是很熟悉，因为我通常使用ActiveX Controls，我发现它更容易与OptionButtons一起使用-我想可能有一种更简洁的方法来处理OptionButtons__。

ReturnFormControlCaption() function:

Function ReturnFormControlCaption(ByVal SheetNameTheControlIsOn As String, ByVal FirstFormControlName As String, _
    Optional ByVal SecondFormControlName As String, Optional ByVal ThirdFormControlName As String, _ 
    Optional ByVal FourthFormControlName As String, Optional ByVal FifthFormControlName As String, _ 
    Optional ByVal SixthFormControlName As String) As String

With ThisWorkbook.Sheets(SheetNameTheControlIsOn)

    If SecondFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not Specified"
        End If
    ElseIf ThirdFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    ElseIf FourthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
     ElseIf FifthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FourthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    ElseIf SixthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FifthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    Else
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FifthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SixthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SixthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    End If
End With

End Function

为了简要解释该函数，您需要传递相关工作表名称的string变量，以及至少一个(最多六个)表单控件名称。

冗长且嵌套的If...ElseIf...Else语句首先被建立到包含了哪个参数。然后，根据哪个参数是第一个空或""值，它将执行下一条If...ElseIf...Else语句，以确定在本例中选择了哪个OptionButton，然后返回该OptionButton的.Caption。

如果未选择任何正在评估的OptionButton，则返回"Not specified“。

注意:此函数用于确定检查了哪个CheckBox，但是如果在您传递的参数中选择了多个，它将只返回第一个被检查的的.Caption。通过一些修改，您可以使函数同时适用于这两种类型，包括所有被检查的CheckBox。

Chip Pearson有一些关于数组以及如何使用数组的优秀信息。您可以在他的网站www.cpearson.com上阅读它们，或者特别是我们在这里针对arrays on this article on his website所做的工作

票数 0

Stack Overflow用户

发布于 2020-04-21 15:18:02

另一种可能更容易实现的方法是使用helper列来“存储”输入值，然后将该范围放入一个数组中，以便直接写入数据库表。

假设您的帮助器列位于名为" helper "__的新工作表上，数据输入位于名为"BBU Quote Entry"的工作表上，并且数据正在移动到BBU Quote Database__。

Sub BBUEntryToDatabaseUsingHelper()

Dim UserInputsArray() As Variant
Dim HelperRange As Range
Dim Destination As Range
Dim LastBBUDatabaseRow As Long
Dim LastHelperRow As Long

With ThisWorkbook.Sheets("Helper")
    LastHelperRow = .Cells(Rows.Count, 2).End(xlUp).Row
    Set HelperRange = .Range("B2:B" & LastHelperRow)
End With

UserInputsArray() = HelperRange.Value

With ThisWorkbook.Sheets("BBU Quote Database")
    LastBBUDatabaseRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    Set Destination = .Cells(LastBBUDatabaseRow, 2)
    Set Destination = Destination.Resize(1, UBound(UserInputsArray, 1))
    Destination.Value = Application.Transpose(UserInputsArray)
End With

End Sub

输入表单：

帮助器列：

这只需在辅助工作表上引用输入工作表中的相关单元格即可。

例如，"Customer Company“值位于工作表BBU Quote Entry上的单元格D6中，因此helper列具有='BBU Quote Entry'!D6

对于“危险”引用，我找到了表单控件OptionButtons链接到的单元格(E74 on Sheet BBU Quote Entry和used =IF('BBU Quote Entry'!E74 = 1, "Hazardous",IF('BBU Quote Entry'!E74 = 2,"Non-Hazardous","Not Specified"))

由于您有一些自定义格式，例如"Desired Clearance“值将输入格式化为#### Inches，引用只返回输入的值，而不返回格式-您可以进一步研究解决这个问题，但同时我在值引用后添加了一个字符串，例如"Desired Clearance”=('BBU Quote Entry'!D15) & " Inches"。

将数据以正确的顺序保存到您的"BBU报价数据库“工作表中，我们只需将"Helper”工作表中的范围直接放入Array()，然后将数组写入"BBU报价数据库“中的正确范围即可。

输出如下所示：