Application.Caller and Activex problem

[Aranell]

Hi guys I need your help again,

I'm creating a sort of Time Sheet, and I need to have for each day per Month a Toggle Button on the sheet. (actually they are 7 buttons for each day).
So I created in Total 217 Buttons, it took me some time to order and resize them and of course to give each one an own name.
I created them as an ActiveX element as I couldn't see in the Form Controls a ToggleButton.

Anyway everything is finish now.
Now I need that each Button do more or less the same except that they write a value in 7 cell in each row (so every button have it's own cell)
I created a module which do the job, but now I don't want to fill the Page code for the specific sheet with 217 Subs for each button

is there a way to have like one sub for all buttons?? or a least one sub for each category of button?
(again I have 7 Buttons: 1x Holidays, 1xVacation, 1xCompany Business, 1x Regular Day Off, 1xSickness, 1xUnpaid and 1xOther)

I want to be able to click for each day if needed one of these option. and at the end of the month I'll have a calculation of the nbr of days of those 7 categories)

If there is no possibility to have one Sub for all buttons or one sub for each category, is there a possibility to get the name of the clicked button, so i can call one module at least?? Unfortunately Application.Caller doesn't work with activex elements.

I hope you didn't got confuse. I have attached my File so far, please ignore the "Summary" sheet and the "Configuration" Sheet. Just the "Jan" sheet.


thanks alot

[Leith Ross]

Hello Aranell,

Wow, 217 controls is lot. The way to handle this issue is by Sub-Classing each group of buttons. This will allow them to respond to events and call the same event code. It's the VBA equivalent of a VB Control Array.

[Norie]

What does clicking each button actually do?

As far as I can tell it justs changes the caption and backcolor of the button, is that right?

Have you considered another approach, perhaps something using the BeforeDoubleClick event?

[LJMetzger]

Hi Aranell,

The following code may help you capture your Active X Toggle Button Events. See the attached working file which uses only 4 Toggle Buttons. Code concepts are courtesy of Andy Pope. Thank you Andy.

In Class Module ClassToggleButtonEvent:

Please Login or Register  to view this content.

In an ordinary Code Module:

Please Login or Register  to view this content.

Please note that I have found 'Active X' to be an inferior solution in most applications, because 'Active X' tends to be unstable and can lead to catastrophic file failure. When I use 'Active X', I always create the controls programmatically during software development (as per the example file), in order to make recovery from an 'Active X' disaster less traumatic.

Lewis

[Aranell]

First thank you all for help, didn't tray the solution yet.
@Leith Ross I tried that but couldn't manage it to work
@Norie: beside changing the backcolor, it also insert a value "1" in a cell when button.value is True, and a "0.5" when the value is False and nothing "" when value is 0.
I need it to calculate the summary of vacation days for example
@LJMetzger thanks alot it's working first it didn't as i wanted but then i changed the Class from myToggleButton_Click() to
myToggleButton_change()
referring to ActiveX.. I couldn't find an Toggle button on the Form Controls, at least not for direct button on the sheet it self.

Beside all this, I couldn't find a better solution than the buttons, to mark the day or 1/2 that day as one of these 7 category mentioned in my first post. I could have made it by inserting like 1 or 2 in a certain cell, but i need it to be by clicking and not by writing something.

[Norie]

All of which could be done using the sheet's BeforeDoubleClick event.

[Aranell]

can you explain more.. how can i do that