Excel vba -get ActiveX Control checkbox when event handler is triggered
- by danoran
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.
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"
'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 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
