User Form Label format (date)

[BenMelrose]

Hi all. Long time watcher first time poster.

Have searched he’d for a few days and am stuck on the below

I have a user form that populates Labels with a date from a cell in a worksheet

the date in the worksheet is in standard format. dd/mm/yy and is imported to the user form with no issue.

The question is how can I format the Userform Label box to only show this date at mmm yyyy

this is the text in the VBA that populates the label

ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Value

this correctly fills the label with dd/mm/yy from the cell L4

i have tried the below

ADD_ACT.Label5 = Format(Date, "mmm yyyy")
ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Format

this correctly formats the date on the userform as mmm yyyy

BUT the form then freezes with runtime error 438 “Object doesn’t support this property or method”

thus us I am lost.

Ben.

 

[steve the fish]

There doesnt seem anything wrong there. What line is causing the error? If you say it correctly formats the date then it must be a subsequent line.

 

[BenMelrose]

There doesnt seem anything wrong there. What line is causing the error? If you say it correctly formats the date then it must be a subsequent line.

steve. Thanks for the reply.


ADD_ACT.Label5 = Format(Date, "mmmm yyyy")
ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Format

Error is on second line. Correct. Got me stuffed ???

if I change the last word from format to value. It works again but does not format the date. ?

 

[steve the fish]

Oh i never noticed that. You can do it all in one line:

ADD_ACT.Label5.Caption = Format(Worksheets("LIST SHEET").Range("L4").Value, "MMM YYYY")

 

[BenMelrose]

Oh i never noticed that. You can do it all in one line:

ADD_ACT.Label5.Caption = Format(Worksheets("LIST SHEET").Range("L4").Value, "MMM YYYY")

Steve. Awesome. Thanks so much. Works a treat. Cheers mate. Ben.