several handlers with same piece of code

[Lberteh]

Hi. I would like to ask for you guys help. I'm a begginer in VBA but I was asked at work to develop a VBA app and I need to finish it asap. I'm trying to learn as fast as I can but all the advanced stuff I've been searching online and writting "dirty code". I don't know exactly what I'm doing but it's working so far. I'm stuck with the following matter. The code below works like a charm.


Private Sub Label3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Label3.ForeColor = vbWhite
Label3.SpecialEffect = 1
Label3.BackStyle = 1
Label3.BackColor = &H808000

End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Label3.ForeColor = vbBlack
Label3.SpecialEffect = 0
Label3.BackStyle = 0

End Sub

My problem is that I need the same piece of code for other 500 Labels in the same userform. I know that it's possible to do it without writting the code for each label separately, but I had no success in the past 2 days. Would you guys help me find a solution? I tried to work with class modules and variables, but it's too advanced for me, I need a Light.

Thank you!

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


class1 code module

Please Login or Register  to view this content.

userform code module

Please Login or Register  to view this content.

[JosephP]

why 500 labels on a form?

[Lberteh]

Thank you so much for your help. It worked perfectly.

I'll keep in mind tagging the code next time, sorry, it was my first post and I admit that I did not read all the instructions before posting.

[Lberteh]

Actually, it's no 500. I forgot to write "like 500" , just wanted to exaggerate a bit. It's 144 the number of labels. I needed something like the month view calendar. but instead if selecting a date, I want to pick a time starting at 07:00 and increasing 5 min. Like 07:00 07:05 07:10 ... 18:55 So I created a userform with all these label, one for each piece of time where you click and fill a text box. that's for daily activities control app. If you have a better suggestion to do this, I'd really appreciate your help.

[JosephP]

wouldn't a combobox or listbox be simpler?

[Lberteh]

Maybe, but If I had to scroll down through a combo or listbox it would take more time than simply typing the time. I wanted a frame with all the available times displayed. Is it possible to be done with a combo or a listbox?

[Andy Pope]

How about this.

Please Login or Register  to view this content.

3 label controls.
labTimesheet contains a picture of all the 5 minute time slots.
labBackground provides visual background for timesheet
labSelect highlights the chosen time slot.

[JosephP]

no it isn't (unless you have a very long frame)

[Lberteh]

Thank you.

That's a much simpler solution.

But when I click a time slot, it gets highlighted and nothing appears. Those two labels are not appearing when I open and try tu use the form.

But for sure, it is much better than witting a code for each time slot label.

[Andy Pope]

"it gets highlighted and nothing appears" probably because no code has been written other than to highlight the slot. It's an example not the finished article.

[Lberteh]

Got it. I'll try to send the information to my textbox,

Thank you for your help.