Excel VBA-将多个用户窗体复选框值写入单个单元格
问题描述:
我想从具有4个复选框选项的用户窗体中传递的值并将它们写入单个并置单元格。Excel VBA-将多个用户窗体复选框值写入单个单元格
当我选择我的窗体像这样:
我想将其保存到这样一个单元格:
我试着用以下完成这一代码(见下文),但它不适用于逗号等,如果只有第二,第三或第四项没有第一项选择。我确信有更好的方法,但我无法弄清楚或在网上找到答案。
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
答
重命名帧控制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
答
如果你想消除输出像最初的逗号“蓝,绿,黄”,你将不得不改变,增加了这些字符串检查colors
是否为空的代码。喜欢的东西:
If chkBlue = true Then
If colors = "" Then
colors = "Blue"
Else
colors = colors & ", Blue"
End If
End If
由于 “红” 是您添加的第一个颜色,你可以假设colors
是空的:
If chkRed = True Then
colors = "Red"
End If
你不”需要Else ... colors = colors
答
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
亚历克斯,那看起来很棒。非常感谢。我会在这里尝试一下,让你知道它是怎么回事。我还没有学会如何这样做,并知道那里肯定有更好的办法。 – ReidC 2013-03-11 17:05:18
工作很好。我必须改变的是为分隔符添加空格以将其从“,”更改为“,” – ReidC 2013-03-11 17:30:28