触发事件处理程序时 Excel vba -get ActiveX Control 复选框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2992433/
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
Excel vba -get ActiveX Control checkbox when event handler is triggered
提问by xdhmoore
I have an Excel spreadsheet that is separated into different sections with named ranges. I want to hide a named range when a checkbox is clicked. I can do this for one checkbox, but I would like to have a single function that can hide the appropriate section based on the calling checkbox. I was planning on calling that function from the event_handlers for when the checkboxes are clicked, and to pass the checkbox as an argument.
我有一个 Excel 电子表格，它被分成具有命名范围的不同部分。我想在单击复选框时隐藏命名范围。我可以为一个复选框执行此操作，但我希望有一个函数可以根据调用复选框隐藏相应的部分。我打算在单击复选框时从 event_handlers 调用该函数，并将复选框作为参数传递。
Is there a way to access the checkbox object that calls the event handler?
有没有办法访问调用事件处理程序的复选框对象？
This works:
这有效：
Sub chkDogsInContest_Click()
ActiveSheet.Names("DogsInContest").RefersToRange.EntireRow.Hidden = Not chkMemberData.Value
End Sub
But this is what I would like to do:
但这就是我想做的：
Sub chkDogsInContest_Click()
Module1.Show_Hide_Section (<calling checkbox>)
End Sub
These functions are defined in a different module:
这些函数在不同的模块中定义：
'The format for the the names of the checkbox controls is
'CHECKBOX_NAME_PREFIX + <name>
'where "name" is also the name of the associated Named Range
Public Const CHECKBOX_NAME_PREFIX As String = "chk"
Public Function CheckName_To_SectionName(ByRef strCheckName As String)
CheckName_To_SectionName = Mid(strCheckName, CHECKBOX_NAME_PREFIX.Length() + 1)
End Function
Public Sub Show_Hide_Section(ByRef chkBox As CheckBox)
ActiveSheet.Names(CheckName_To_SectionName(chkBox.Name())).RefersTo.EntireRow.Hidden = True
End Sub
采纳答案by Lance Roberts
Since you're using regular (Active-X) checkboxes on a normal worksheet, then your best bet is to create a Click event for each sub, then call one routine for the Hide with the parameter of the checkbox name, like:
由于您在普通工作表上使用常规（Active-X）复选框，因此最好的办法是为每个子创建一个 Click 事件，然后使用复选框名称的参数为 Hide 调用一个例程，例如：
Private Sub chkCheckBox1_Click()
If chkCheckBox1.Value = True Then
Call RangeHide("CheckBox1")
End If
End Sub
Private Sub RangeHide(rangetohide As String)
Range(rangetohide).EntireRow.Hidden = True
End Sub
回答by xdhmoore
I think the answer is to create another class that has a checkbox object as a part of it and declare that object "WithEvents" Then I can create a method chkBox_clicked() that will be called whenever any checkbox that is a member of that class is clicked. I can also store the range within the object.
我认为答案是创建另一个类，其中包含一个复选框对象，并声明该对象为“WithEvents”点击。我还可以将范围存储在对象中。
http://www.cpearson.com/excel/Events.aspx
http://www.cpearson.com/excel/Events.aspx
Has more info... Great site btw for excel VBA.
有更多信息......顺便说一句，excel VBA 的好网站。
EDIT: This does not work. See my comment below.
编辑：这不起作用。看我下面的评论。
