Excel VBA - 将多个用户表单复选框值写入单个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15343473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Writing multiple userform checkbox values to a single cell
提问by ReidC
I am trying to take the values passed from a userform that has 4 checkbox options and write them to a single concatenated cell.
我正在尝试获取从具有 4 个复选框选项的用户表单传递的值，并将它们写入单个串联单元格。
When I select my userform like this:
当我像这样选择我的用户表单时：
I would like to save it to a single cell like this:
我想将它保存到这样的单个单元格中：
I tried accomplishing this with the following code (see below), but it doesn't work quite right with the commas and such if only the 2nd, 3rd, or 4th item is chosen without the first. I am convinced there is a better way but I can't figure it out or find an answer online.
我尝试使用以下代码（见下文）来完成此操作，但如果仅选择第 2、3 或 4 项而没有选择第一个，则使用逗号就无法正常工作。我相信有更好的方法，但我无法弄清楚或在网上找到答案。
Private Sub cmdSave_Click()
Dim colors As String
If chkRed = True Then
colors = "Red"
Else
colors = colors
End If
If chkBlue = True Then
colors = colors & ", Blue"
Else
colors = colors
End If
If chkGreen = True Then
colors = colors & ", Green"
Else
colors = colors
End If
If chkYellow = True Then
colors = colors & ", Yellow"
Else
colors = colors
End If
With colorsSheet
.Cells(ActiveCell.Row, 1).Value = colors
End With
Unload Me
End Sub
回答by Alex K.
Rename the frame control to frameColoursthen you can loop its checkboxes & build your string;
将框架控件重命名为frameColours然后您可以循环其复选框并构建您的字符串；
Dim chk as Control
Dim colors as string, delimiter as string
for Each chk In Me.frameColours.Controls
if typeOf chk Is msforms.CheckBox then
if (chk.Value) then
colors = colors & delimiter & chk.Caption
delimiter = ","
end if
end if
next
With colorsSheet
.Cells(ActiveCell.Row, 1).Value = colors
End With
回答by mac
Private Sub Submit_Click()
Dim lngIndex As Long, strTemp As String
For lngIndex = 1 To 16
'There are 16 check boxex, hence 1 to 16 is given
If Me.Controls("CheckBox" & lngIndex) Then
strTemp = strTemp & Me.Controls("TextBox" & lngIndex).Value & vbLf
'Returns you the output in a new line within the same cell.
End If
Next lngIndex
Sheets("Sheet1").Range("A1").Value = Left$(strTemp, Len(strTemp) - 1)
End Sub
回答by Tim
If you want to eliminate the initial comma from output like ", Blue, Green, Yellow", you're going to have to change the code that adds those strings to check whether colorsis empty. Something like:
如果您想从“, Blue, Green, Yellow”等输出中消除初始逗号，您将不得不更改添加这些字符串的代码以检查是否colors为空。就像是：
If chkBlue = true Then
If colors = "" Then
colors = "Blue"
Else
colors = colors & ", Blue"
End If
End If
Since "Red" is the first color you add, you can assume that colorsis empty:
由于“红色”是您添加的第一种颜色，您可以假设它colors是空的：
If chkRed = True Then
colors = "Red"
End If
You dont' need Else ... colors = colors
你不需要 Else ... colors = colors
