将 VBA 表单中的复选框值插入 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13141683/
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
Inserting checkbox values from VBA form into Excel Spreadsheet
提问by methuselah
I am trying to insert a list of selected checkboxes into a spreadsheet, within this use case, a user can choose up to 15 items. This will be inserted into a certain cell which I have defined below.
我正在尝试将选定复选框列表插入电子表格，在此用例中，用户最多可以选择 15 个项目。这将被插入到我在下面定义的某个单元格中。
I have a checkbox with the following names/values:
我有一个带有以下名称/值的复选框：
Name Value
========== =====
chk_week1 1
chk_week2 2
... ...
... ...
chk_week15 15
For example if the user selects chk_week1, chk_week2, chk_week4 and chk_week5, then it should be inserted into the cell as 1,2,4,5.
例如，如果用户选择 chk_week1、chk_week2、chk_week4 和 chk_week5，则应将其插入单元格中为 1、2、4、5。
I've included an image how it looks like to better demonstrate it:
我已经包含了一张图片来更好地展示它的样子：
Each checkbox has the name and value listed in the table above. Here is the code I am using so far:
每个复选框都具有上表中列出的名称和值。这是我目前使用的代码：
Private Sub btnSubmit_Click()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Worksheets("main")
' Copy the data to the database
' Get last empty cell in column A
Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
' Having difficulty adding the code here
' rng1.Offset(1, 7) = weeks
End Sub
Thanks in advance.
提前致谢。
回答by Daniel
This function would return the string you're wanting to put in the cell.
此函数将返回您想要放入单元格的字符串。
Function CheckBoxValues() As String
For x = 1 To 15
If Sheets("Main").Shapes("chk_week" & x).OLEFormat.Object.Object.Value Then
CheckBoxValues = CheckBoxValues & x & ","
End If
Next
if Len(CheckBoxValue <> 0) then
CheckBoxValues = Left(CheckBoxValues, Len(CheckBoxValues) - 1)
end if
End Function
Or for the non-looping method, check Francis Dean's solution.
或者对于非循环方法，请查看 Francis Dean 的解决方案。
回答by Francis Dean
You can use a function to go through your check boxes and return a string in your desired format as such (add on the rest of the check boxes!)
你可以使用一个函数来检查你的复选框并以你想要的格式返回一个字符串（添加其余的复选框！）
Private Sub btnSubmit_Click()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Worksheets("main")
' Copy the data to the database
' Get last empty cell in column A
Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
' Having difficulty adding the code here
rng1.Offset(1, 7) = GetWeeks
End Sub
Private Function GetWeeks() As String
Dim weeks As String
'Add values to the string if condition is true
If chk_week1.Value = True Then weeks = weeks & "1,"
If chk_week2.Value = True Then weeks = weeks & "2,"
If chk_week3.Value = True Then weeks = weeks & "2,"
'...
If chk_week14.Value = True Then weeks = weeks & "14,"
If chk_week15.Value = True Then weeks = weeks & "15,"
'Remove the trailing comma
If Right(weeks, 1) = "," Then weeks = Left(weeks, Len(weeks) - 1)
GetWeeks = weeks
End Function
Private Sub btnSubmit_Click()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Worksheets("main")
' Copy the data to the database
' Get last empty cell in column A
Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
' Having difficulty adding the code here
rng1.Offset(1, 7) = GetWeeks
End Sub
Private Function GetWeeks() As String
Dim weeks As String
'Add values to the string if condition is true
If chk_week1.Value = True Then weeks = weeks & "1,"
If chk_week2.Value = True Then weeks = weeks & "2,"
If chk_week3.Value = True Then weeks = weeks & "2,"
'...
If chk_week14.Value = True Then weeks = weeks & "14,"
If chk_week15.Value = True Then weeks = weeks & "15,"
'Remove the trailing comma
If Right(weeks, 1) = "," Then weeks = Left(weeks, Len(weeks) - 1)
GetWeeks = weeks
End Function
