我有一个VBA问题,我们正在寻找一个快速,优雅的解决方案。
问题涉及入住框:他们的选择和去选择。
说我们从一行和四列数据开始(单元格A1:
A B Star D
我们有三个未检查的框: Galaxy
, Universe
, Planet
。
检查Galaxy
后,该行被动态复制(下一个空行)
A B Star D
A B Galaxy D
如果我们检查了所有三个:
A B Star D
A B Galaxy D
A B UniverseD
A B Planet D
现在,如果我们取消检查Universe
..自动省略该行:
A B Star D
A B Galaxy D
A B Planet D
我喜欢这种设置的实时"您所看到的"。我不能采用一种优雅的方法来编码吗?
非常感谢。
Function GetDataBasedOnSelection(ByVal galaxyChecked As Boolean, ByVal universeChecked As Boolean, _
ByVal planetChecked As Boolean) As Variant
Dim currentRow As Integer
Dim retVal(1 To 3, 1 To 4)
currentRow = 1
If galaxyChecked Then
GoSub fillInCommonValuesInThisRow
retVal(currentRow, 3) = "Galaxy"
currentRow = currentRow + 1
End If
If universeChecked Then
GoSub fillInCommonValuesInThisRow
retVal(currentRow, 3) = "Universe"
currentRow = currentRow + 1
End If
If planetChecked Then
GoSub fillInCommonValuesInThisRow
retVal(currentRow, 3) = "Planet"
currentRow = currentRow + 1
End If
GetDataBasedOnSelection = retVal
Exit Function
fillInCommonValuesInThisRow:
retVal(currentRow, 1) = "A"
retVal(currentRow, 2) = "B"
retVal(currentRow, 3) = ""
retVal(currentRow, 4) = "D"
Return
End Function
如何使用上述方法:
'Pass the actual selection (true if the checkbox is checked, false otherwise)
Range("A2:D4").Value = GetDataBasedOnSelection(True, False, True)