|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub InsertCheckboxesAndHideText()
- Dim ws As Worksheet
- Dim col As Long
- Dim row As Long
- Dim cb As Object
- Dim colArray As Variant
- Dim i As Long
- Dim FC As FormatCondition, sCell As String, eCell As String
- ' 设置要操作的工作表
- Set ws = ThisWorkbook.Sheets("初始赋值")
-
- ' 指定需要插入复选框的列:A, C, E, G, ..., Q(隔列)
- colArray = Array(1, 3, 5, 7, 9, 11, 13, 15, 17) ' 这些是列号
- Application.ScreenUpdating = False
- ' 遍历指定列并在第2行到第16行中插入复选框
- For i = LBound(colArray) To UBound(colArray)
- col = colArray(i)
-
- ' 在每列的2到16行中插入复选框
- With ws.Cells(row, col)
- For row = 2 To 17
- ' 插入复选框
- Set cb = ws.CheckBoxes.Add(.Left, .Top, .Width, .Height)
- cb.Caption = "" ' 去掉复选框的标题
- cb.linkedCell = .Address ' 将复选框与所在单元格链接
- cb.Display3DShading = True ' 设置复选框为三维阴影效果
-
- ' 设置单元格字体颜色为与背景相同,使TRUE或FALSE不可见
- .Font.Color = .Interior.Color ' 字体颜色设置为与背景颜色相同
- Next row
- End With
- ' ** 设置条件格式
- With ws.Cells(2, col + 1).Resize(15, 1)
- sCell = .Cells(1).Offset(0, -1).Address(0, 1)
- eCell = .Cells(.Cells.Count).Offset(1, -1).Address(1, 0)
- Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=OR(" & sCell & "," & eCell & ")")
- FC.Interior.Color = vbGreen
- End With
- Next i
- Application.ScreenUpdating = True
- End Sub
- Sub RemoveFC()
- Dim FC
- For Each FC In ActiveSheet.Cells.FormatConditions
- FC.Delete
- Next
- End Sub
- Sub 删除所有复选框() ' 简化代码
- Dim ws As Worksheet
- Dim cb As CheckBox
-
- ' 设置要操作的工作表
- Set ws = ThisWorkbook.Sheets("初始赋值")
- ws.DrawingObjects.Delete
- ' 遍历并删除工作表中的所有复选框
- ' For Each cb In ws.CheckBoxes
- ' cb.Delete
- ' Next cb
- End Sub
复制代码 |
|