Label_Click executes sub twice

[d365b]

Hi everyone,

I am using the solution suggested by Colin Legg in the thread below

Userform Label Click problem

i have created some dynamic labels on a userform however when i go to link them to with let’s say line1_click it does not recognize it albeit i know that label is definitely there. Is there a setting or something i am missing in the properties. This is how the label is created Set newlabel =...

www.mrexcel.com

but when I call a sub within the Label_Click sub

Private Sub pLabel_Click()
    clsCalendar.ClickEventHandler (pLabel.Name)
End Sub

the clsCalendar.ClickEventHandler gets executed twice. Any idea why this is happening? Is there a way to avoid that or a suitable workaround?

Many thanks for all your suggestions!

 

[GWteB]

The name of clsCalendar.ClickEventHandler suggests that it's an event handler.
Event handlers are automatically invoked by Excel when the event in question occurs.
If multiple handlers are implemented for the exact same type of event, they will be executed sequentially.

In your example, the Label_Click event handler apparently invokes another Label_Click handler, causing it to run twice.

 

[d365b]

The name of clsCalendar.ClickEventHandler suggests that it's an event handler.
Event handlers are automatically invoked by Excel when the event in question occurs.
If multiple handlers are implemented for the exact same type of event, they will be executed sequentially.

In your example, the Label_Click event handler apparently invokes another Label_Click handler, causing it to run twice.

Hi GWteB,

thank you for replying to my thread! Well, the Label_Click only has to capture the name of the label that was clicked and pass it on to the ClickEventHandler which carries out different tasks according to the label that was clicked.

I am trying to build a MonthView from scratch and could not figure out how to have the Label_Click handler in the Calendar class. I had to have a separate class for the label. I had to use a flag (boolRunOnce) as a workaround. Unfortunately, I cannot upload the workbook here: it would have been much easier to explain myself.

 

[RoryA]

What does the code for clsCalendar.ClickEventHandler look like? I suspect your implementation has gone amiss somewhere but it may be hard to diagnose without all the code (you can always upload to a site like Onedrive or Dropbox and post a link here?). Also note that there is no need for the parentheses around pLabel.Name.

 

[d365b]

What does the code for clsCalendar.ClickEventHandler look like? I suspect your implementation has gone amiss somewhere but it may be hard to diagnose without all the code (you can always upload to a site like Onedrive or Dropbox and post a link here?). Also note that there is no need for the parentheses around pLabel.Name.

Right, I could share it as you suggested RoryA.

Here's the link.

 

[GWteB]

I've downloaded your file, will reply shortly

 

[d365b]

I've downloaded your file, will reply shortly

Thanks! Bear in mind: I have learned everything on YouTube and Excel forums ??

 

[d365b]

I've downloaded your file, will reply shortly

In principle, it should work like the MonthView/DatePicker no longer available in Office 365 64-bit.

 

[GWteB]

No doubt you will have noticed that the phenomenon you described only occurred when clicking on labels that act as navigation keys (forward, back, up and down). The reason for this was the following.

The LoadView method of the Calendar class turned out to be responsible for two tasks:
- initialization when opening the userform;
- updating the view in response of user input by practically rebuilding your entire userform.

Because the latter was triggered by a click event, the code of that click event handler was kept alive while a new event handler was implemented at the same time. Only after the very last code of the event handler had been executed the code got out of scope and the corresponding class was terminated. At the time of this termination, the newly supplied event handler for the event in question was already present so it was executed by Excel, making it look like the same code was being executed twice, but in fact it was a different instance of that class.

I made some minor changes of which the most eye-catching are marked with @@@@@. The two tasks are now in two separate procedures: a public Init method and a private LoadView method. The Init method is responsible for initializing the permanent Label controls, the LoadView for the temporary ones. So there are also two collections used for storage: one to keep the permanent instances of the cLabel class, and one for the temporary ones. You might consider to make the date labels permanent as well and only changing the caption of those labels in response to user input.

I'm aware that the changes I've made might not be very attractive on the surface, nevertheless they solve your issue.

Finally, a few notes. Disabling Excel events has no effect on the events of ActiveX controls on a userform. Excel's screen updating doesn't care about any dynamic changes to a userform and Excel's calculation event is only fired if something actually changes on a worksheet, and therefore has no influence on the performance of any change of a userform. So where you thought you would achieve a performance improvement, you actually got a slight performance penalty.


Example Workbook

 

[d365b]

No doubt you will have noticed that the phenomenon you described only occurred when clicking on labels that act as navigation keys (forward, back, up and down). The reason for this was the following.

The LoadView method of the Calendar class turned out to be responsible for two tasks:
- initialization when opening the userform;
- updating the view in response of user input by practically rebuilding your entire userform.

Because the latter was triggered by a click event, the code of that click event handler was kept alive while a new event handler was implemented at the same time. Only after the very last code of the event handler had been executed the code got out of scope and the corresponding class was terminated. At the time of this termination, the newly supplied event handler for the event in question was already present so it was executed by Excel, making it look like the same code was being executed twice, but in fact it was a different instance of that class.

I made some minor changes of which the most eye-catching are marked with @@@@@. The two tasks are now in two separate procedures: a public Init method and a private LoadView method. The Init method is responsible for initializing the permanent Label controls, the LoadView for the temporary ones. So there are also two collections used for storage: one to keep the permanent instances of the cLabel class, and one for the temporary ones. You might consider to make the date labels permanent as well and only changing the caption of those labels in response to user input.

I'm aware that the changes I've made might not be very attractive on the surface, nevertheless they solve your issue.

Finally, a few notes. Disabling Excel events has no effect on the events of ActiveX controls on a userform. Excel's screen updating doesn't care about any dynamic changes to a userform and Excel's calculation event is only fired if something actually changes on a worksheet, and therefore has no influence on the performance of any change of a userform. So where you thought you would achieve a performance improvement, you actually got a slight performance penalty.


Example Workbook

Wow! It is way more fluent than it used to be ... that's right: it only happened when clicking the navigation key labels. I am going to try making the changes you suggested: keeping all the labels (which will be added via the Init method) and changing only the caption (via the LoadView method). Next, I'll try to make it pop-up when double-clicking on a cell ... lots to do ?

Many thanks!