Userform Controls VBA

[bewsh1987]

Hi All,

Need your help I have 48 Labels on a userform with the name Day1,Day2,Day 3 Ect. Ect. Day47 and Day48

I want to avoid having 48 Sub Procedures example below

Private Sub Day1_Click()
Userform1.Hide
'>>>Call Function and Call Sub Will Go Here<<<
End Sub

Is there a way to have a userform complete a procedure action based on what label was clicked maybe an event handler?

Thank you

Ian

 

[Andrew Poulsom]

You can pass the Control to a Procedure as an argument.

 

[My Aswer Is This]

Yes clicking on a label can cause some event to happen.
What do you want to happen when you click on label "Day1" and "Day2"

We may be able to show you how to perform your event without writing 48 procedures.

 

[bewsh1987]

Thank you for your reply's

I Have my function built and work's fine, that is if I write each sub procedure on the user form I just need to workout how to complete this with one event handler to save maintenance time in future.

 

[Andrew Poulsom]

Can you post your Function please, and how you are calling it?

 

[bewsh1987]

Function partNames() As String

Dim daypickerdate As String
Dim Month_A As String
Dim Year_A As String
Dim i As Integer

Month_A = Month1.Caption ' Month Name E.G january
Year_A = Year1.Caption   ' Year Nymber E.G 2016

daypickerdate = Format(DateValue("01/" & Month_A & "/" & Year_A), "ddd")

Select Case daypickerdate
Case Is = "Mon"
partNames = "7"
Case Is = "Tue"
partNames = "1"
Case Is = "Wed"
partNames = "2"
Case Is = "Thu"
partNames = "3"
Case Is = "Fri"
partNames = "4"
Case Is = "Sat"
partNames = "5"
Case Is = "Sun"
partNames = "6"
End Select

For i = 0 To 41

DayDate = Day(DateValue("01/" & Month_A & "/" & Year_A) - partNames + i)
UserForm1.Controls("Day" & i + 1).Caption = DayDate

If Format(DateValue("01/" & Month_A & "/" & Year_A) - partNames + i, "mmmm") = Month1 Then
UserForm1.Controls("Day" & i + 1).ForeColor = &H808080
Else
UserForm1.Controls("Day" & i + 1).ForeColor = &H80000012
End If

Next i

End Function

Private Sub Day1_Click()
Userform1.Hide
Call partNames
End Sub

 

[My Aswer Is This]

That is why I asked what you were wanting the function to do.
We cannot give to advice when we have no knowledge of what your function needs to do.

 

[Rick Rothstein]

Function partNames() As String

Dim daypickerdate As String
Dim Month_A As String
Dim Year_A As String
Dim i As Integer

Month_A = Month1.Caption ' Month Name E.G january
Year_A = Year1.Caption   ' Year Nymber E.G 2016

[COLOR="#FF0000"][B]daypickerdate = Format(DateValue("01/" & Month_A & "/" & Year_A), "ddd")

Select Case daypickerdate
Case Is = "Mon"
partNames = "7"
Case Is = "Tue"
partNames = "1"
Case Is = "Wed"
partNames = "2"
Case Is = "Thu"
partNames = "3"
Case Is = "Fri"
partNames = "4"
Case Is = "Sat"
partNames = "5"
Case Is = "Sun"
partNames = "6"
End Select[/B][/COLOR]
For i = 0 To 41

DayDate = Day(DateValue("01/" & Month_A & "/" & Year_A) - partNames + i)
UserForm1.Controls("Day" & i + 1).Caption = DayDate

If Format(DateValue("01/" & Month_A & "/" & Year_A) - partNames + i, "mmmm") = Month1 Then
UserForm1.Controls("Day" & i + 1).ForeColor = &H808080
Else
UserForm1.Controls("Day" & i + 1).ForeColor = &H80000012
End If

Next i

End Function

It appears that the daypickerdate variable is used only in the Select Case statement and nowhere else. If that is true, then all the code I highlighted in red can be replaced by this single line of code...

partNames = Weekday(DateValue("01/" & Month_A & "/" & Year_A), vbTuesday)

 

[hatman]

1) I assume that the function is in your Userform CLass Module. Declare it as a Friend routine.

2) Create a class module called clsLabel_Event, and put in the following code

Option Explicit

Private WithEvents ob As MSForms.Label
Private CallBackParent As [userform_name]


Private Sub ob_Click()

    callbackparent.hide
    Call CallBackParent.partNames()

End Sub

Friend Sub WatchControl(oControl As MSForms.Label)

    Set ob = oControl
    Set CallBackParent = ob.parent

End Sub

3)add the following code to your userform class module (I'm free forming this w/o testing, so be sure to debug before running):

option explicit

Private collLabs As Collection

Private Sub UserForm_Initialize()

    dim ctrl as msforms.control
    Dim OBE As clsLabel_Event

    Set collLabs = New Collection

    for each ctrl in me.controls

        if typename(ctrl) = "Label" then

           Set OBE = New clsLabel_Event
        
           collLabs.Add OBE
        
            Call OBE.WatchControl(ctrl)

        end if

    next ctrl

end sub

 

[bewsh1987]

It appears that the daypickerdate variable is used only in the Select Case statement and nowhere else. If that is true, then all the code I highlighted in red can be replaced by this single line of code...

partNames = Weekday(DateValue("01/" & Month_A & "/" & Year_A), vbTuesday)

Rick. Excellent this makes sense my function has been updated cheers

hatman thank you for this I will apply and try this when I get home in about two hours and report back

Thanks all