Excel VBA - 将多个用户表单复选框值写入单个单元格

Question

我正在尝试获取从具有 4 个复选框选项的用户窗体传递的值并将它们写入单个连接单元格。

当我像这样选择我的用户表单时:



我想将它保存到这样的单个单元格中:



我尝试使用以下代码完成此操作(见下文)，但如果仅选择第 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

Answer 1

将框架控件重命名为 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