Need help changing the Label Caption in Userform

spycein

Board Regular
Joined
Mar 8, 2014
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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:

VBA Code:
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
 
Upvote 0
Solution
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

Rich (BB code):
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top