通过VBA检测复选框的更改 [英] Detecting changes to checkboxes via VBA

问题描述

从我以前的问题开始，

客户的要求是在报表上添加复选框，以禁用其他工作表上的信息行。行被定义为由P_XXXXXX形成的命名范围。 XXXXXX是一个唯一的标识符，也是该行上的一个字段，因此我可以轻松地生成范围名称。

我遇到的问题是： p>

• 点击项目后关闭表单Excel会询问我们是否要保存。这是不可取的。

我需要注意在生成的复选框上发生更改事件。所以如果一个或多个更改可以通过并隐藏/取消隐藏相关范围。

我添加复选框的代码如下所示：

 '对于列17（= Q）中的每一行... 
 
'复选框。 
 Dim lCenter As Long 
 lCenter = rngCurrent.Width / 4'不实际中心但足够接近
 With ActiveSheet.CheckBoxes.Add（rngCurrent.Left + lCenter，rngCurrent.Top  -  2，rngCurrent .Width，rngCurrent.Height）
 .Interior.ColorIndex = xlNone 
 .Caption =
结束
  

那么如何将复选框的更改与子/功能相链接？

解决方案

将Checkboxes对象的OnAction属性设置为选中或取消选中复选框时要运行的子名称。

  Sub MakeCB（）
 
使用ActiveSheet.CheckBoxes.Add（ActiveCell.Left + 0，ActiveCell.Top  -  2，ActiveCell.Width，ActiveCell.Height）
 .Interior.ColorIndex = xlNone 
 .Caption =
 .OnAction =CheckboxChange
 End with 
 
 End Sub 
 
 Sub CheckboxChange（）
 
 MsgBoxchange
 
 End Sub  

Following on from my previous question.

A requirement from the customer is to have checkboxes on a report to disable rows of information on another sheet. The rows are defined as named ranges, formated by P_XXXXXX. The XXXXXX is a unique identifier that is also a field on the row so I can easily generate the range names on the fly.

The problem I am having is:

• After clicking on the items and then closing the form Excel asks if we want to save. This is undersirable.

I need someway of registering a change event happening on my generated checkboxes. So if one or more changes I can run through and hide/unhide the relevant ranges.

My code for adding the checkboxes looks like:

        ' For each row...

        ' check box in column 17(=Q).
        Dim lCenter As Long
        lCenter = rngCurrent.Width / 4 ' not actual centre but close enough
        With ActiveSheet.CheckBoxes.Add(rngCurrent.Left + lCenter, rngCurrent.Top - 2, rngCurrent.Width, rngCurrent.Height)
            .Interior.ColorIndex = xlNone
            .Caption = ""
        End With

So how do you link a change in a checkbox with a sub/function?

解决方案

Set the OnAction property of the Checkboxes object to the name of a sub you want to run whenever the checkbox is checked or unchecked.

Sub MakeCB()

    With ActiveSheet.CheckBoxes.Add(ActiveCell.Left + 0, ActiveCell.Top - 2, ActiveCell.Width, ActiveCell.Height)
        .Interior.ColorIndex = xlNone
        .Caption = ""
        .OnAction = "CheckboxChange"
    End With

End Sub

Sub CheckboxChange()

    MsgBox "change"

End Sub

这篇关于通过VBA检测复选框的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！