Classes in Excel VBA

I am trying to build an event for check boxes that are created at run time on an excel worksheet.

Here is the class that I created called "myCheckBox"

Public WithEvents cBoxObject As Msforms.CheckBox

Private Sub cBoxObject_Click()
MsgBox cBoxOject.Name
End Sub

Open in new window

In my code module, I am creating a new check-box and trying to set it to the one in my class to inherit the click event.

Dim tbCollection As New Collection

Sub macro1()
Dim cBox As Object
Dim checkBoxObject As New myCheckBox

Set cBox = ActiveSheet.OLEObjects.Add("Forms.CheckBox.1", Left:=Range("A1"))

Set checkBoxObject.cBoxObject = cBox.Object

tbCollection.Add cbox

End Sub

Open in new window

When I click on the check box, nothing happens.

Any help would be appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Isn't this all too complicated?
The OnAction property will be inherited by a copy of a Checkbox. Therefore, creating a copy of a checkbox at runtime is as simple as Copy/Paste. The pasted copy will have the same OnAction property as the original.

it seems to be a problem of the way Excel routes the events. It is not a problem of your code (except the missing "b" in the MsgBox). If you create a userform and use your code to catch the click event of the checkbox created in the userform the code works. You can also move the code to a worksheet code (which is a class module in opposite to the standard module) to try to catch the click event with "WithEvents" directly which also do not work.

I would say the problem is in the way the checkbox is inserted into the Excel sheet. Excel creates a generic ActiveX container for any ActiveX control which only has the events GotFocus and LostFocus. This container seems to block any event which is coming from the inner ActiveX object. The only exception is if you directly insert the click event into the sheet's code using the object name, that works.

So I would say, you can't do that with ActiveX objects in Excel (it works in Access forms, in Excel userforms, but not in an Excel sheet). Maybe someone else has an idea but my tests all failed.


byundtMechanical EngineerCommented:
I think that Faustulus has identified the better fix for the problem: use Forms control checkboxes rather than ActiveX checkboxes.

You can assign a macro to a Forms control checkbox. In fact, you can assign the same macro to more than one Forms control checkboxes. There is no need for the class module in such a case.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jnsimexAuthor Commented:
@Faustulus - Your solution sounds much more cleaner than mine. Can you provide some sample code if that is not too much trouble to ask?

@Bitsqueezer - thank you for the explanation. It makes more sense now that you explained it in detail. As a note, I noticed that the event is captured if the checkbox is created a design time.

@byundt - Yes, after reading his reply, he has provided a better fix for the problem.
byundtMechanical EngineerCommented:
You use Application.Caller to get the name of a shape object that called a macro. So you use that property to figure out which checkbox was checked/unchecked.

Create a set of Forms toolbar checkboxes on a worksheet, then assign the same macro to each of them (by rightclicking the checkbox and choosing "Assign Macro..."). Put the macro in a regular module sheet.
Private Sub cBoxObject_Click()
MsgBox Application.Caller & " status is " & IIf(ActiveSheet.CheckBoxes(Application.Caller).Value = 1, "Checked", "Unchecked")
End Sub

Open in new window

Could you help out with some code as requested? I'm travelling and unable to oblige.
jnsimexAuthor Commented:
@byundt - I got it to work using your code. That was very helpful. My only outstanding issue is with the linked cell. I want the check box to remember its last value but every time I run the macro, the value in the linked cell is reset because I am creating the boxes at run time.

I tried storing the value of the linked cells in a array and applying them back after the check boxes are created but the onaction event only fires when someone clicks on the check box even though a checkmark is generated.
byundtMechanical EngineerCommented:
I agree that the OnAction event sub won't run if you check the box or change the value of its linked cell programmatically. But I'm still not understanding your problem. If you can create the checkbox programmatically and set its value, why can't you call its OnAction event sub programmatically?

Or perhaps you could trap the change in value of the linked cell using a Worksheet_Change event sub. This will be trapped when you change the linked cell either manually or using a macro. It will not be trapped when you check or uncheck the checkbox.
'This sub must go in code pane of worksheet containing the checkboxes
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Cells(1, 1).Address
Case "$D$2"
    MsgBox "Link cell status is " & IIf(ActiveSheet.CheckBoxes("Check Box 1").Value = 1, "Checked", "Unchecked")
Case "$D$4"
    MsgBox "Link cell status is " & IIf(ActiveSheet.CheckBoxes("Check Box 2").Value = 1, "Checked", "Unchecked")
End Select
End Sub

Open in new window

If you name the checkboxes according to their linked cell address (e.g. cb$D$2) then you could generalize the above code so it is good for all checkboxes with:
'This sub must go in code pane of worksheet containing checkboxes & linked cells
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cb As CheckBox
With Target.Cells(1, 1)
    On Error Resume Next
    Set cb = CheckBoxes("cb" & .Address)
    On Error GoTo 0
    If Not cb Is Nothing Then
        MsgBox "Link cell status is " & .Value & vbLf & _
            "  The associated checkbox will be changed to " & IIf(cb.Value = 1, "Checked", "Unchecked")
    End If
End With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jnsimexAuthor Commented:
byundt - my problem was trapping the onaction event but I did what you recommended by trapping it in the worksheet_change event and now everything is working.

Thank you for all your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.