jnsimex
asked on
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"
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.
When I click on the check box, nothing happens.
Any help would be appreciated.
Here is the class that I created called "myCheckBox"
Public WithEvents cBoxObject As Msforms.CheckBox
Private Sub cBoxObject_Click()
MsgBox cBoxOject.Name
End SubIn 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 SubWhen I click on the check box, nothing happens.
Any help would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
@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.
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.
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
@byundt
Could you help out with some code as requested? I'm travelling and unable to oblige.
Could you help out with some code as requested? I'm travelling and unable to oblige.
ASKER
@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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thank you for all your help!
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.