Classes in Excel VBA

jnsimex
jnsimex used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Hi,

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.

Cheers,

Christian
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

@byundt
Could you help out with some code as requested? I'm travelling and unable to oblige.

Author

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.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
jnsimex,
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

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial