Excel vba -get ActiveX Control checkbox when event handler is triggered

Question

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"




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

Answer 1

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:

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

Answer 2

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.

http://www.cpearson.com/excel/Events.aspx

Has more info... Great site btw for excel VBA.

EDIT: This does not work. See my comment below.