将包含真/假的 Excel 单元格转换为 VBA 中的复选框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14378137/
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
Convert an excel cell containing true/false as check box from VBA
提问by Manas Saha
I have a column in a excel cell which has either "True" or "false" as text.
我在 Excel 单元格中有一个列，该列将“真”或“假”作为文本。
The column is populated by a VBA code.
该列由 VBA 代码填充。
I need to display a check box as checked instead of True, and a check box unchecked instead of False.
我需要将复选框显示为已选中而不是 True，以及未选中的复选框而不是 False。
Can this be done from VBA code? Suppose the cell containing the value is refered as
这可以通过 VBA 代码完成吗？假设包含该值的单元格被称为
ActiveWorkBook.Sheets("mySheetName").cells(i, 4)
ActiveWorkBook.Sheets("mySheetName").cells(i, 4)
i = Row Index, which is inside a loop.
i = Row Index，它在一个循环内。
采纳答案by Peter Albert
This code should do the job, just change the For loop according to your needs:
这段代码应该可以完成这项工作，只需根据您的需要更改 For 循环：
Sub subPlaceCheckbox()
Const cDblCheckboxWidth As Double = 15
Const cStrCheckboxPrefix As String = "cb4_"
Dim cb As CheckBox
Dim rng As Range
Dim i As Long
Application.ScreenUpdating = False
'First, delete all old checkboxes to avoid doubles
For Each cb In Sheet1.CheckBoxes
If Left(cb.Name, Len(cStrCheckboxPrefix)) = _
cStrCheckboxPrefix Then
cb.Delete
End If
Next
For i = 1 To 20
Set rng = Sheet1.Cells(i, 4)
'Place checkbox
Set cb = Sheet1.CheckBoxes.Add( _
rng.Left + rng.Width / 2 - cDblCheckboxWidth / 2, _
rng.Top, cDblCheckboxWidth, rng.Height)
'Set checkbox properties
With cb
.Name = "cb_" & rng.Address(False, False)
.Value = rng.Value
.LinkedCell = rng.Address
.Display3DShading = True
.Characters.Text = ""
End With
'Hide content of cell
rng.NumberFormat = ";;;"
Next i
Application.ScreenUpdating = True
End Sub
回答by Vinny Roe
You can just leave the cell saying TRUE or FALSE, add the checkbox to your sheet, and set the LinkedCell on the checkbox to be your original cell. If you don't want to see the cell, place the checkbox over the top of it!
您可以将单元格保留为 TRUE 或 FALSE，将复选框添加到您的工作表中，然后将复选框上的 LinkedCell 设置为您的原始单元格。如果您不想看到单元格，请将复选框放在它的顶部！
