Excel VBA脚本插入多个复选框链接到单元格是yes和no而不是true和false [英] Excel VBA script to insert multiple checkboxes linked to cell with yes and no instead of true and false

问题描述

我正在研究一个大量的excel文件，我需要插入多个单元格与复选框，我需要这些框附加到它们出现的单元格，我需要输出说清除或现在他们说对或错。到目前为止，我有以下代码大量生产单元格，但现在我需要调整这个代码来将输出更改为已清除或代替True或False。

  Sub AddCheckBoxes（）
 
 Dim cb As CheckBox 
 Dim myRange As Range，cel As Range 
 Dim wks As工作表
 
设置wks =表（Sheet1）
 
设置myRange = wks.Range（A1：A1000）
 
对于每个cel在myRange 
 
设置cb = wks.CheckBoxes.Add（cel.Left，cel.Top，30，6）
 
 
带有cb 
 
 .Caption =
 .LinkedCell = cel.Address 
 
结束
 
下一个
 
 End Sub 
  

任何人都可以帮我看看吗？

解决方案

  Sub AddCheckBoxes（）
 
 Dim cb As CheckBox 
 Dim myRange As Range，cel As Range 
 Dim wks As Worksheet 
 
设置wks =表（Sheet1）
 
设置myRange = wks.Range（A1：A1000）
 
对于每个cel在myRange 
 
设置cb = wks.CheckBoxes.Add（cel.Left，cel.Top，30,6）
 
带有cb 
 .Caption =
 .OnAction =ProcessCheckBox
结束
 
下一个
 
 End Sub 
 
 Sub ProcessCheckBox（）
 Dim cb As CheckBox 
带表格（Sheet1）
设置cb = .CheckBoxes（Application.Caller）
如果不是cb是没有，则cb.TopLeftCell = IIf（cb.Value = 1，已清除，）
结束
结束Sub 
  

重要提示 ProcessCheckBox（） / code>模块必须在标准模块中。如果是工作表模块，您将收到以下消息：

如果您使代码更灵活，您可以在Select Case语句中使用组合框的索引或名称来决定最终的输出结果。

< hr>

  Sub ProcessCheckBox（）
 Dim cb As CheckBox 
 
带表格（Sheet1）
 
设置cb = .CheckBoxes（Application.Caller）
如果不是cb是没有，然后
 
选择案例cb.Index 
案例1，2，4 
 cb.TopLeftCell = IIf（cb.Value = 1，Cleared，）
案例3，5，7 
 cb.TopLeftCell = IIf（cb.Value = 1，1 ，0）
 Case Else 
 cb.TopLeftCell = IIf（cb.Value = 1，True，False）
结束选择
 
如果
 
结束
 End Sub 
  

I am working on a massive excel file were I need to insert multiple cells with checkboxes and I need those boxes attached to the cell they appear over and I need the output to say "Cleared" or "" verses currently they say "True" or "False". So far I have the following code to mass produce the cells but now I need to tweak this code to change the output to say "Cleared" or "" verses "True" or "False".

Sub AddCheckBoxes()

Dim cb As CheckBox
Dim myRange As Range, cel As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1") 

Set myRange = wks.Range("A1:A1000")

For Each cel In myRange

    Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)


    With cb

        .Caption = ""
        .LinkedCell = cel.Address

    End With

Next

End Sub

Can anyone help me figure this out?

解决方案

Sub AddCheckBoxes()

    Dim cb As CheckBox
    Dim myRange As Range, cel As Range
    Dim wks As Worksheet

    Set wks = Sheets("Sheet1")

    Set myRange = wks.Range("A1:A1000")

    For Each cel In myRange

        Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)

        With cb
            .Caption = ""
            .OnAction = "ProcessCheckBox"
        End With

    Next

End Sub

Sub ProcessCheckBox()
    Dim cb As CheckBox
    With Sheets("Sheet1")
        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
    End With
End Sub

Important: The ProcessCheckBox() module has to be in a standard module. If it is a worksheet module you will receive this message:

If you to make the code more flexible you can use the combo-box's index or name in a Select Case statement to decide on what your final output will be.

---

Sub ProcessCheckBox()
    Dim cb As CheckBox

    With Sheets("Sheet1")

        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then

            Select Case cb.Index
            Case 1, 2, 4
                cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
            Case 3, 5, 7
                cb.TopLeftCell = IIf(cb.Value = 1, 1, 0)
            Case Else
                cb.TopLeftCell = IIf(cb.Value = 1, True, False)
            End Select

        End If

    End With
End Sub

这篇关于Excel VBA脚本插入多个复选框链接到单元格是yes和no而不是true和false的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！