Need help changing the Label Caption in Userform

[spycein]

Hello Everyone,
I am looking for a solution to change the caption of a label based on two combo box values.
I have two combo boxes with names defined as Year & Month and one label as lbl1.
My condition would be if Year.Value = "" or Month.Value ="" then lbl1.Caption = ""
Else
lbl1.Caption = Year.Value & "-" & Month.Value

I have put this above code under UserForm Initialization(), but nothing happens when I select data on both combo boxes.

Really Appreciate your help on this.

Best Regards,
Shib

 

[HaHoBe]

Hi Shib,

the Initialize will only be called once, you would need to put the code into the Change-Events of the ComboBoxes in order to make them work:

Private Sub UserForm_Initialize()
'only on start of UF
If Year.Value = "" Or Month.Value = "" Then
  lbl1.Caption = ""
Else
  lbl1.Caption = Year.Value & "-" & Month.Value
End If
End Sub

Private Sub Year_Change()
'on every change in CB
If Year.Value = "" Or Month.Value = "" Then
  lbl1.Caption = ""
Else
  lbl1.Caption = Year.Value & "-" & Month.Value
End If
End Sub

Private Sub Month_Change()
If Year.Value = "" Or Month.Value = "" Then
  lbl1.Caption = ""
Else
  lbl1.Caption = Year.Value & "-" & Month.Value
End If
End Sub

Ciao,
Holger

 

[dmt32]

Hi,

The Initialize event only fires once when userform is first loaded – you would need to use the Change events of both Month & Year comboboxes.

Note though that Year & Month are function names of the application and I would suggest that you change your combobox names as follows to avoid any possible conflicts

cboMonth

cboYear

Place all codes in your userforms code page

Private Sub cboMonth_Change()
    GetMonthYear
End Sub

Private Sub cboYear_Change()
    GetMonthYear
End Sub

Private Sub UserForm_Initialize()
    Me.cboYear = Year(Date)
    Me.cboMonth = MonthName(Month(Date), True)
End Sub


Sub GetMonthYear()
    Dim mydate As Variant
    mydate = Me.cboYear.Value & "-" & Me.cboMonth.Value
    Me.lbl1.Caption = IIf(IsDate(mydate), mydate, "")
End Sub

I have assumed that you are using month names in your month combobox which are abbreviated e.g Jan, Feb etc.
If using the full name then Change True shown in RED to False

Dave

 

[spycein]

Thank you so much @HaHoBe and @dmt32 !!