Excel VBA-将多个用户窗体复选框值写入单个单元格 [英] Excel VBA - Writing multiple userform checkbox values to a single cell

问题描述

我试图从具有4个复选框选项的用户窗体中传递的值并将它们写入单个串联的单元格中。

I am trying to take the values passed from a userform that has 4 checkbox options and write them to a single concatenated cell.

当我选择这样的用户窗体时：

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.

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 ，然后可以循环其复选框&构建您的字符串；

Rename the frame control to frameColours then you can loop its checkboxes & build your string;

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

这篇关于Excel VBA-将多个用户窗体复选框值写入单个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！